Mícéal Gallagher in Store procedure 2 minutes

Postgres - Creating Store Procedure

Before we look at creating a store procedure, it should be mentioned that debugging these can be a real pain, so try and avoid making them overly complicated. Store procedures offer a lot of benefits over SQL execution; these benefits will not be covered in this post but there is a great article here. The main advantage store procedures bring to the database is their ability to manipulate and collate data before returning to the caller. The basic idiom of a store procedure is as follows:

CREATE OR REPLACE FUNCTION sp_store_procedure()
  RETURNS refcursor AS
$BODY$
DECLARE
	-- Variable declarations
BEGIN
	-- P/SQL
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Returning Data

Lets say we want a store procedure that takes a user ID and returns the full name in the result set. Notice in the example below that we had to declare ref refcursor; this will become important when we execute the store procedure.

CREATE OR REPLACE FUNCTION sp_get_user_fullname(userId integer)
  RETURNS refcursor AS
$BODY$
DECLARE
	ref refcursor;
BEGIN
	OPEN REF FOR
		SELECT first_name || ' ' || last_name
		FROM users
		WHERE id = userId;
	RETURN ref;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Executing the stored procedure

SELECT sp_get_user_fullname(1);

The result set:

----------------------
|sp_get_user_fullname |
|refcursor            |
----------------------
| unnamed portal 4    |
----------------------

Cursors

The result from a store procedure is returned as a cursor which is fine if you are a programmatic call but not so good if you are a human. To get the result in a human readable format we need to modify our store procedure to accept the name of a cursor. Notice how ref refcursor moves from the DECLARE section to the parameter list.

CREATE OR REPLACE FUNCTION sp_get_user_fullname(userId integer, ref refcursor)
  RETURNS refcursor AS
$BODY$
DECLARE
	-- Variable declarations
BEGIN
	OPEN REF FOR -- Open the cursor
		SELECT first_name || ' ' || last_name
		FROM users
		WHERE id = userId;
	RETURN ref; -- Return the cursor to caller
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Now we call the store procedure like so:

SELECT sp_get_user_fullname(1, 'miceal');
FETCH ALL IN "miceal";

Summary

Store procedures are a great way to avoid the overhead associated with collating multiple database calls by returning a single (or sometimes multiple) result set. Cursors are used to return result sets; the difficulty is that you need to predict the name of the cursor used to access the records. When an application calls the store procedure, the DB library takes care of resolving the cursor name to the result set, hence the reason why we have to explicitly specify the cursor name. References: PostgreSQL - How to Return a Result Set from a Stored Procedure