john.parnefjord.se Reflections & findings

As a new user of Postgresql, version 8.1, I quickly ran into trouble when I just wanted to get the results from a table using an ordinary select in a procedure. There are some tricks that one has to know of.

Let's start from the very beginning by creating a new example table that will hold the example data.

CREATE TABLE test_procedure
(
   id SERIAL, 
   firstname VARCHAR(50) DEFAULT NULL, 
   lastname VARCHAR(50) DEFAULT NULL
);

Now add some data:

INSERT INTO test_procedure (firstname,lastname) VALUES ('John','Parnefjord');
INSERT INTO test_procedure (firstname,lastname) VALUES ('Mike','Lee');
INSERT INTO test_procedure (firstname,lastname) VALUES ('Sally','Johnson');
INSERT INTO test_procedure (firstname,lastname) VALUES ('Mark','Jones');

To return data rows from this table from a procedure one can use the following code:

CREATE OR REPLACE FUNCTION get_all_data() 
RETURNS SETOF test_procedure
AS
    'SELECT * FROM test_procedure;'
LANGUAGE sql VOLATILE SECURITY DEFINER;

In order to return multiple rows one has to use the keyword SETOF. Following SETOF is the name of the table from where data is to be selected. Run the procedure:

select * from get_all_data();

The result set returned should look something like this.

Quite often only a portion of all data in a table has to be returned and I alwas try to specify exactly which columns to return. So the obvious conclusion would be to replace asterisk (*) with the columns id and firstname and this how the new procedure would look like.

CREATE OR REPLACE FUNCTION get_firstnames() 
RETURNS SETOF test_procedure
AS
    'SELECT id, firstname FROM test_procedure;'
LANGUAGE sql VOLATILE SECURITY DEFINER;

Trying to run this code yields an error:

ERROR: return type mismatch in function declared to return test_procedure
SQL state: 42P13
Detail: Final SELECT returns too few columns.
Context: SQL function "get_firstnames"

As soon as one just want to return a subset of the data in the table one has to specify the data type to be returned explictly in the following fashion and the type returned is changed from the table name to the generic RECORD:

CREATE OR REPLACE FUNCTION get_firstnames(OUT id INTEGER, OUT firstname VARCHAR(50)) 
RETURNS SETOF RECORD
AS
    'SELECT id, firstname FROM test_procedure;'
LANGUAGE SQL SECURITY DEFINER;

Now run the new procedure:

select * from get_firstnames();

The procedure should now return the data: