PostgresSQL Procedures This editor formats code poorly. Please see the linked file: create_function_2.sql You will complete the procedure: proc_insertGam

PostgresSQL Procedures This editor formats code poorly. Please see the linked file: create_function_2.sql

You will complete the procedure: proc_insertGam

Click here to Order a Custom answer to this Question from our writers. It’s fast and plagiarism-free.

PostgresSQL Procedures This editor formats code poorly. Please see the linked file: create_function_2.sql

You will complete the procedure: proc_insertGame beginning on line 120.

Test your code, save it, and upload the SQL script.

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. /*
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_p1NameP_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;
$$;

Place your order now for a similar assignment and have exceptional work written by one of our experts, guaranteeing you an A result.

Need an Essay Written?

This sample is available to anyone. If you want a unique paper order it from one of our professional writers.

Get help with your academic paper right away

Quality & Timely Delivery

Free Editing & Plagiarism Check

Security, Privacy & Confidentiality