Return multiple fields as a record in PostgreSQL with PL/pgSQL

Question

I am writing a SP, using PL/pgSQL.
I want to return a record, comprised of fields from several different tables. Could look something like this:

CREATE OR REPLACE FUNCTION get_object_fields(name text)   RETURNS RECORD AS $$ BEGIN   -- fetch fields f1, f2 and f3 from table t1   -- fetch fields f4, f5 from table t2   -- fetch fields f6, f7 and f8 from table t3   -- return fields f1 ... f8 as a record END $$ language plpgsql;  

How may I return the fields from different tables as fields in a single record?

[Edit]

I have realized that the example I gave above was slightly too simplistic. Some of the fields I need to be retrieving, will be saved as separate rows in the database table being queried, but I want to return them in the 'flattened' record structure.

The code below should help illustrate further:

CREATE TABLE user (id int, school_id int, name varchar(32));  CREATE TYPE my_type AS (   user1_id   int,   user1_name varchar(32),   user2_id   int,   user2_name varchar(32) );  CREATE OR REPLACE FUNCTION get_two_users_from_school(schoolid int)   RETURNS my_type AS $$ DECLARE   result my_type;   temp_result user; BEGIN   -- for purpose of this question assume 2 rows returned   SELECT id, name INTO temp_result FROM user where school_id = schoolid LIMIT 2;   -- Will the (pseudo)code below work?:   result.user1_id := temp_result[0].id ;   result.user1_name := temp_result[0].name ;   result.user2_id := temp_result[1].id ;   result.user2_name := temp_result[1].name ;   return result ; END $$ language plpgsql 

Answer 1

You need to define a new type and define your function to return that type.

CREATE TYPE my_type AS (f1 varchar(10), f2 varchar(10) /* , ... */ );  CREATE OR REPLACE FUNCTION get_object_fields(name text)  RETURNS my_type  AS  $$  DECLARE   result_record my_type;  BEGIN   SELECT f1, f2, f3   INTO result_record.f1, result_record.f2, result_record.f3   FROM table1   WHERE pk_col = 42;    SELECT f3    INTO result_record.f3   FROM table2   WHERE pk_col = 24;    RETURN result_record;  END $$ LANGUAGE plpgsql;  

If you want to return more than one record you need to define the function as returns setof my_type

---

Update

Another option is to use RETURNS TABLE() instead of creating a TYPE which was introduced in Postgres 8.4

CREATE OR REPLACE FUNCTION get_object_fields(name text)    RETURNS TABLE (f1 varchar(10), f2 varchar(10) /* , ... */ ) ... 

Answer 2

Don't use CREATE TYPE to return a polymorphic result. Use and abuse the RECORD type instead. Check it out:

CREATE FUNCTION test_ret(a TEXT, b TEXT) RETURNS RECORD AS $$ DECLARE    ret RECORD; BEGIN   -- Arbitrary expression to change the first parameter   IF LENGTH(a) < LENGTH(b) THEN       SELECT TRUE, a || b, 'a shorter than b' INTO ret;   ELSE       SELECT FALSE, b || a INTO ret;   END IF; RETURN ret; END;$$ LANGUAGE plpgsql; 

Pay attention to the fact that it can optionally return two or three columns depending on the input.

test=> SELECT test_ret('foo','barbaz');              test_ret              ----------------------------------  (t,foobarbaz,"a shorter than b") (1 row)  test=> SELECT test_ret('barbaz','foo');              test_ret              ----------------------------------  (f,foobarbaz) (1 row) 

This does wreak havoc on code, so do use a consistent number of columns, but it's ridiculously handy for returning optional error messages with the first parameter returning the success of the operation. Rewritten using a consistent number of columns:

CREATE FUNCTION test_ret(a TEXT, b TEXT) RETURNS RECORD AS $$ DECLARE    ret RECORD; BEGIN   -- Note the CASTING being done for the 2nd and 3rd elements of the RECORD   IF LENGTH(a) < LENGTH(b) THEN       ret := (TRUE, (a || b)::TEXT, 'a shorter than b'::TEXT);   ELSE       ret := (FALSE, (b || a)::TEXT, NULL::TEXT);    END IF; RETURN ret; END;$$ LANGUAGE plpgsql; 

Almost to epic hotness:

test=> SELECT test_ret('foobar','bar');    test_ret     ----------------  (f,barfoobar,) (1 row)  test=> SELECT test_ret('foo','barbaz');              test_ret              ----------------------------------  (t,foobarbaz,"a shorter than b") (1 row) 

But how do you split that out in to multiple rows so that your ORM layer of choice can convert the values in to your language of choice's native data types? The hotness:

test=> SELECT a, b, c FROM test_ret('foo','barbaz') AS (a BOOL, b TEXT, c TEXT);  a |     b     |        c          ---+-----------+------------------  t | foobarbaz | a shorter than b (1 row)  test=> SELECT a, b, c FROM test_ret('foobar','bar') AS (a BOOL, b TEXT, c TEXT);  a |     b     | c  ---+-----------+---  f | barfoobar |  (1 row) 

This is one of the coolest and most underused features in PostgreSQL. Please spread the word.