Return user-defined sets in PostgreSQL

Returning sets of data with PL/pgSQL functions can be somewhat tricky. The documentation has a line saying how to do it somewhere, but the beginner SQL user (like the author of this post) might be confused at the impressive amount of information given by this document.

This post is thus intended as an example that firsts creates a small database and a function that manipulates data. Let's do this!

First, let's create two tables with some data in them.

CREATE TABLE foo (
"IndexFoo" integer DEFAULT nextval('"sq_IndexFoo"'::regclass) NOT NULL,
"FOO_IndexCol1" integer NOT NULL,
"FOO_Timestamp" timestamp without time zone DEFAULT now()
);

And a table that is pointed to by foo:

CREATE TABLE foo_info (
"IndexInfoFoo" integer DEFAULT nextval('"sq_IndexFooInfo"'::regclass) NOT NULL,
"INF_Answer" integer DEFAULT 42
);

Now say we want to return rows from both tables. Say we want the answer, given in foo_info and the timestamp, given in foo. We just have to create a function that does just

CREATE OR REPLACE FUNCTION "f_whatIsTheAnswer"(OUT answer integer, OUT "time" timestamp without time zone)

RETURNS SETOF record AS $$

BEGIN

RETURN QUERY SELECT fi."INF_Answer", f."FOO_Timestamp"
FROM foo f

JOIN "foo_info" fi ON f."FOO_IndexCol1" = fi."IndexInfoFoo";
END;
$$ LANGUAGE plpgsql;

Notice how the returned columns are defined in the function's arguments. This defines the column set returned by the function. If left out, PostgreSQL will complain that the record type has not been assigned any return type.

Because we use PL/pgSQL, the RETURN QUERY statement is used. Since this is a simple query with no complex data manipulation, it could have been a simple SQL request. The RETURN QUERY would have been unnecessary.

Try it yourself! Here's the database dump.

dump.sql