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

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.user开发者_如何学JAVA1_name := temp_result[0].name ;
  result.user2_id := temp_result[1].id ;
  result.user2_name := temp_result[1].name ;
  return result ;
END
$$ language plpgsql

---

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 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<>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

---

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; 

---

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);

---

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)

---

CREATE TABLE users(user_id int, school_id int, name text);
insert into users values (1, 10,'alice')
,(5, 10,'boy')
,(13, 10,'cassey')
,(17, 10,'delores')
,(4, 11,'elaine');

I setted the user_id as arbitrary int. The function input parameter is the school_id. So if the school_id is 10 you hope to get the following result:

 user_id | name  | user_id | name
---------+-------+---------+------
       1 | alice |       5 | boy

So your query should be something like:

with a as (
select u1.user_id,
     u1.name from  users u1 
        where school_id = 10 order by user_id limit 1),
b as      
(select u2.user_id,u2.name from users u2 
        where school_id = 10  order by user_id limit 1 offset 1 )
select * from a  cross JOIN b ;

So let's wrap the query to the plpgsql function.

CREATE OR REPLACE FUNCTION 
    get_object_fields2(_school_id int)
  RETURNS TABLE (user1_id   int
               , user1_name text
               , user2_id   int
               , user2_name text) 
               LANGUAGE plpgsql AS 
$func$
DECLARE countu integer;
BEGIN
    countu := (
        select count(*) from users where school_id = _school_id);
    IF countu >= 2 THEN
        RETURN QUERY
            with a as (
            select u1.user_id,
                u1.name from  users u1 
                where school_id = _school_id 
                    order by user_id limit 1),
            b as(
                select u2.user_id,u2.name from users u2 
                where school_id = _school_id 
                    order by user_id limit 1 offset 1 )
            select * from a  cross JOIN b;
    elseif countu = 1 then
    return query
      select u1.user_id, u1.name,u1.user_id, u1.name
        from  users u1 where school_id = _school_id; 
    else 
        RAISE EXCEPTION 'not found';
    end if;
END
$func$;