In a follow up to our initial Stored Procedures article, here we present some more complex examples based on real-world work we’ve completed on a PostgreSQL 8.3 database.
Fetching A Newly Inserted ID
Often times you will want to insert a record into a table that has a default ID string. There is any easy method for doing this with the INSERT…RETURNING syntax.
In this stored procedure example below we add a new group name to our groups table and get back the auto-generated ID (primary key) in the newid variable. We then use that to insert a record in our group members table AND return the newly created ID back from the stored procedure, making it easy for the calling program to catch the new ID:
-
DECLARE
-
newid BIGINT;
-
BEGIN
-
INSERT INTO groups (group_name) VALUES ($1) RETURNING group_id INTO newid;
-
INSERT INTO group_members (group_id, avatar_id,groupmember_admin) VALUES (newid,$2,‘t’);
-
RETURN newid;
-
END
Select Loop With String Building
This first example will select a set of records in the child table of a one-to-many relationship (the many part) and build up semi-colon separated string comprised of the name field for each child record. The name field contains the human-readable name of on item a person is wearing.
sp_what_am_i_wearing(<id>)
definition
-
CREATE OR REPLACE FUNCTION sp_what_am_i_wearing(id bigint) RETURNS text AS $$
-
DECLARE
-
retval text = ”;
-
row_data UserRow%ROWTYPE;
-
BEGIN
-
FOR row_data IN SELECT * FROM MyObjects WHERE id = $1
-
LOOP
-
retval := retval || row_data.object_name || ‘;’;
-
END LOOP;
-
RETURN retval;
-
END;
-
$$ LANGUAGE plpgsql;
Usage and Output
game_data=# select sp_what_am_i_wearing(2); avatariswearing-----------------------------------------------------------------hat_01;shirt_01;pants_22;shoes_32;Insert/Update Loop With String Decomposition
This second example is the “setter” companion to our prior example’s “getter” functionality. This version breaks down a human-readable string with names separated by colons and then updates the child table in the one-to-many relationship. We not only have to add records to that table for any new objects we might be wearing but also remove any pre-existing records from our prior wardrobe.
First we select any object that is not in our character string but IS in the child objects table, loop through those records, and delte them. In essence we are deleting anything we are no longer wearing. This may be ALL of the items we were wearing previously or just a single item if we only changed something like our hat or shoes.
In the second part of the procedure we loop through all the objects in the string as we break it down based on the semicolon, and only select those items that are NOT left remaining in the child table of our one-to-many relationship. Remember, that child table now contains only those items we were wearing before and did not change. If that child set is empty we’ll end up adding new records for EVERY object in our string. We fetch the ID of the objects we are wearing from the objects table and then insert our user ID, the object ID, and a flag in the child table saying that we are wearing the various objects.
sp_set_what_i_wear(<id>,<wearing_string>)
Definition
-
CREATE OR REPLACE FUNCTION sp_set_what_i_wear(id bigint, wearing text) RETURNS void AS $$
-
DECLARE
-
ao_id BIGINT;
-
obj_id BIGINT;
-
BEGIN
-
– Delete what I used to be wearing, but am no longer wearing
-
–
-
FOR ao_id IN SELECT object_id FROM ObjectList
-
WHERE id=$1 AND name NOT IN (SELECT regexp_split_to_table($2,E‘;’))
-
LOOP
-
DELETE FROM MyObjects WHERE object_id = ao_id;
-
END LOOP;
-
-
– Fetch the object IDs of things I am now wearing, but don’t show up in my object list
-
– Insert the object IDs into that child table, called "MyObjects" that links me to stuff I wear
-
–
-
FOR obj_id IN SELECT object_id FROM Objects
-
WHERE name IN (SELECT regexp_split_to_table($2,E‘;’))
-
AND name NOT IN (SELECT name FROM ObjectList WHERE id=$1)
-
LOOP
-
INSERT INTO MyObjects (id,object_id,onperson) VALUES ($1,obj_id,‘t’);
-
END LOOP;
-
END;
-
$$ LANGUAGE plpgsql;
Usage and Output
Note: current return type is VOID.
It may be wise to set a return value showing the items that were in the set string but don’t exist in the DB. This can be done with a simple select from objects not in select from regexp_split_to_table. This is a performance hit, so it was left out for this revision.
game_data=# select sp_set_what_i_wear(2,'hat_01;shirt_10;pants_11;shoes_33;');
sp_set_avatar_is_wearing
--------------------------(1 row)