PostgresSQL Procedures

profilenoahsark
create_Function_2.sql

/* Homework Start with this file and complete the procedure: proc_insertGame Write the code to call the procedure and test it. You may change the table definition if you have a good reason... document it! Document your error codes. Place all of the code in one SQL file and submit via Canvas. Remember that I'm fussy about format! This is *not* the obfuscated C contest! The prcedure proc_insertGame begins about line 118. \pset pager off create database rps; -- connect to rps \c rps; DROP SEQUENCE IF EXISTS pk_seq; CREATE SEQUENCE pk_seq; DROP TABLE IF EXISTS tbl_game; DROP TABLE IF EXISTS tbl_player; CREATE TABLE tbl_player ( fld_pName VARCHAR(16), fld_pTimeStamp TIMESTAMP, -- -- other fields as appropriate -- CONSTRAINT cst_player_PK PRIMARY KEY(fld_pName) ); -- ------------------------------------------- CREATE OR REPLACE PROCEDURE proc_insertPlayer ( P_pName IN VARCHAR(16), P_errCode INOUT SMALLINT ) LANGUAGE plpgsql AS $$ DECLARE -- variable declarations (if any) go here BEGIN IF EXISTS ( SELECT * FROM tbl_player WHERE fld_pName=P_pName ) THEN P_errCode = 1; ELSE INSERT INTO tbl_player(fld_pName, fld_pTimeStamp) VALUES(P_pName, CURRENT_TIMESTAMP); P_errCode = 0; END IF; END; $$; -- -------------------------------------------- -- Calling a procedure with an output parameter. DO $$ DECLARE myErrCode SMALLINT; BEGIN CALL proc_insertPlayer('Al', myErrCode); RAISE NOTICE '%', myErrCode; -- this is about as much console output as it has. END; $$; -- -------------------------------------------- -- next... \pset pager off -- connect to rps \c rps; DROP TABLE IF EXISTS tbl_game; CREATE TABLE tbl_game ( fld_gid INTEGER, fld_p1Name VARCHAR(16), fld_p2Name VARCHAR(16), fld_gTimeStamp TIMESTAMP, -- CONSTRAINT cst_game_PK PRIMARY KEY(fld_gid), -- CONSTRAINT cst_nameOrder CHECK(fld_p1Name<fld_p2Name), -- CONSTRAINT cst_nullPlayers CHECK(fld_p1Name IS NOT NULL AND fld_p2Name IS NOT NULL); -- CONSTRAINT cst_uniqueGame UNIQUE(fld_p1Name,fld_p2Name), -- CONSTRAINT cst_p1FK FOREIGN KEY(fld_p1Name) REFERENCES tbl_player(fld_pName), -- CONSTRAINT cst_p2FK FOREIGN KEY(fld_p2Name) REFERENCES tbl_player(fld_pName) ); -- --------------------------------------------- -- THE ASSIGNMENT BEGINS HERE CREATE OR REPLACE PROCEDURE proc_insertGame ( P_p1Name IN VARCHAR(16), P_p2Name IN VARCHAR(16), P_errCode INOUT SMALLINT ) LANGUAGE plpgsql AS $$ DECLARE t VARCHAR(16); BEGIN -- If the parameters are out of order, swap them IF P_p1Name>P_p2Name THEN t=P_p1Name; P_p1Name=P_p2Name; P_p2Name=t; END IF; -- no ELSE here (This seems to work, not an error) -- not all SSIPL implementations will allow you to change a value parameter. -- check that neither are NULL -- ELSE -- check that they're not equal -- ELSE -- check that p1 exists in tbl_player IF NOT EXISTS ( SELECT * FROM tbl_player WHERE fld_pName=P_p1Name ) THEN P_errCode=13; -- it's whatever code you say it is; FK violation END IF; -- this, just so it'll compile; it's not the end, yet. -- ELSE -- check that p2 exists in tbl_player -- ELSE Insert into game /* INSERT INTO tbl_game( fld_gid INTEGER, fld_p1Name VARCHAR(16), fld_p2Name VARCHAR(16), fld_gTimeStamp ) VALUES( nextval('pk_seq'), P_p1Name, P_p2Name, CURRENT_TIMESTAMP ); P_errCode=0; -- no errors */ END; $$;