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

User · Accepted Answer

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

User · Answer

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   lt  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  gt  SELECT test ret  foo   barbaz                 test ret              ----------------------------------   t foobarbaz  a shorter than b    1 row   test  gt  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   lt  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  gt  SELECT test ret  foobar   bar       test ret     ----------------   f barfoobar    1 row   test  gt  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  gt  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  gt  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

User · Answer

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 optional END  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 lt  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

User · Answer

You can achieve this by using simply as a returns set of records using return query   CREATE OR REPLACE FUNCTION schemaName get two users from school schoolid bigint   RETURNS SETOF record  LANGUAGE plpgsql AS  function  begin   return query   SELECT id  name FROM schemaName user where school id   schoolid   end   function    And call this function as   select   from schemaName get two users from school schoolid  as x a bigint  b varchar

User · Answer

you can do this using OUT parameter and CROSS JOIN  CREATE OR REPLACE FUNCTION get object fields my name text  OUT f1 text  OUT f2 text  AS    SELECT t1 name  t2 name FROM  table1 t1  CROSS JOIN table2 t2  WHERE t1 name   my name AND t2 name   my name     LANGUAGE SQL    then use it as a table   select get object fields   Pending     get object fields -------------------  Pending code   1 row    or  select   from get object fields   Pending    f1        f --------- --------- Pending   code  1 row    or  select  get object fields   Pending    f1  f1 --------- Pending  1 row

User · Answer

If you have a table with this exact record layout  use its name as a type  otherwise you will have to declare the type explicitly   CREATE OR REPLACE FUNCTION get object fields                   name text           RETURNS mytable AS            DECLARE f1 INT          DECLARE f2 INT                      DECLARE f8 INT          DECLARE retval mytable          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                 retval     f1  f2       f8                   RETURN retval          END    language plpgsql

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

Examples related to sql

Examples related to postgresql

Examples related to stored-procedures

Examples related to types

Examples related to plpgsql