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

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.

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) /* , ... */ )...

To return a single row

Simpler with OUT parameters:

CREATE OR REPLACE FUNCTION get_object_fields(_school_id int                                       , OUT user1_id   int                                       , OUT user1_name varchar(32)                                       , OUT user2_id   int                                       , OUT user2_name varchar(32)) AS $func$BEGIN   SELECT INTO user1_id, user1_name          u.id, u.name   FROM   users u   WHERE  u.school_id = _school_id   LIMIT  1;  -- make sure query returns 1 row - better in a more deterministic way?   user2_id := user1_id + 1; -- some calculation   SELECT INTO user2_name          u.name          FROM   users u   WHERE  u.id = user2_id;END$func$  LANGUAGE plpgsql;

Call:

SELECT * FROM get_object_fields(1);

• You don't need to create a type just for the sake of this plpgsql function. It may be useful if you want to bind multiple functions to the same composite type. Else, OUT parameters do the job.

• There is no RETURN statement. OUT parameters are returned automatically with this form that returns a single row. RETURN is optional.

• Since OUT parameters are visible everywhere inside the function body (and can be used just like any other variable), make sure to table-qualify columns of the same name to avoid naming conflicts! (Better yet, use distinct names to begin with.)

Simpler yet - also to return 0-n rows

Typically, this can be simpler and faster if queries in the function body can be combined. And you can use RETURNS TABLE() (since Postgres 8.4, long before the question was asked) to return 0-n rows.

The example from above can be written as:

CREATE OR REPLACE FUNCTION get_object_fields2(_school_id int)  RETURNS TABLE (user1_id   int               , user1_name varchar(32)               , user2_id   int               , user2_name varchar(32)) AS $func$BEGIN   RETURN QUERY   SELECT u1.id, u1.name, u2.id, u2.name   FROM   users u1   JOIN   users u2 ON u2.id = u1.id + 1   WHERE  u1.school_id = _school_id   LIMIT  1;  -- may be optionalEND$func$  LANGUAGE plpgsql;

Call:

SELECT * FROM get_object_fields2(1);

• RETURNS TABLE is effectively the same as having a bunch of OUT parameters combined with RETURNS SETOF record, just shorter.

• The major difference: this function can return 0, 1 or many rows, while the first version always returns 1 row.
  Add LIMIT 1 like demonstrated to only allow 0 or 1 row.

• RETURN QUERY is simple way to return results from a query directly.
  You can use multiple instances in a single function to add more rows to the output.

db<>fiddle here (demonstrating both)

Varying row-type

If your function is supposed to dynamically return results with a different row-type depending on the input, read more here:

• Refactor a PL/pgSQL function to return the output of various SELECT queries