Return multiple fields as an entry in PostgreSQL using PL / pgSQL

Question

I am writing SP using PL / pgSQL.
I want to return a record consisting of fields from several different tables. It might 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 can I return fields from different tables as fields in one record?

[change]

I realized that the above example was a little too simplistic. Some of the fields that I need to get will be saved as separate rows in the requested database database, but I want to return them in a “flattened” record structure.

Following is the following code:

 CREATE TABLE user (id int, school_id int, name varchar(32)); CREATE TYPE my_type ( 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 a function to return this 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

---

Refresh

Another option is to use RETURNS TABLE() instead of creating the TYPE that 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

Do not use CREATE TYPE to return a polymorphic result. Use the record type> instead . Check this:

 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; 

Note that it can return two or three columns if necessary, 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 is detrimental to the code, so use a consistent number of columns, but it is ridiculously convenient 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 the epic heat:

 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 this into several lines so that your ORM selection can convert values ​​to your own data type selection language? Heat:

 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.

Answer 3

It might be easier with the OUT options :

 CREATE OR REPLACE FUNCTION get_object_fields( name text ,OUT user1_id int ,OUT user1_name varchar(32) ,OUT user2_id int ,OUT user2_name varchar(32) ) AS $func$ BEGIN SELECT t.user1_id, t.user1_name INTO user1_id, user1_name FROM tbl1 t WHERE t.tbl1_id = 42; user2_id := user1_id + 43; -- some calculation SELECT t.user2_name INTO user2_name FROM tbl2 t WHERE t.tbl2_i = user2_id; END $func$ LANGUAGE plpgsql; 

• You do not need to create a type just for this plpgsql function. This can be useful if you want to associate several functions with the same type. I rarely use it since OUT parameters are added.

• As you may have noticed, there is no RETURN instruction. OUT parameters are returned automatically; no RETURN statement is required.

• Since OUT parameters are visible everywhere inside the function body (and can be used just like any other variable), make sure that the columns have the same name to avoid name conflicts.

Simplification yet - or returning multiple lines

In most cases this can be simplified. Sometimes queries in the body of a function can be combined, which is usually (not always) faster. And you can use RETURNS TABLE() - introduced with Postgres 8.4 (long before this question was also asked).

The example above can be rewritten as:

 CREATE OR REPLACE FUNCTION get_object_fields(name text) RETURNS TABLE ( user1_id int ,user1_name varchar(32) ,user2_id int ,user2_name varchar(32)) AS $func$ BEGIN RETURN QUERY SELECT t1.user1_id, t1.user1_name, t2.user2_id, t2.user2_name FROM tbl1 t1 JOIN tbl2 t2 ON t2.user2_id = t1.user1_id + 43 WHERE t1.tbl1_id = 42 LIMIT 1; -- may be optional END $func$ LANGUAGE plpgsql; 

• RETURNS TABLE actually matches the OUT parameter set in combination with the RETURNS record , a bit shorter / more elegant.

• The main difference is that this function can return 0, 1 or many rows, and the first version always returns 1 row.
  If you want to make sure that this returns only 0 or 1 row, add LIMIT 1 as shown.

• RETURN QUERY is a very convenient modern way to directly return query results.
  You can use multiple instances in one function to add more lines to the output.

Different line types

If your function should dynamically return results with different types of strings , depending on the input, read here:

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

Answer 4

If you have a table with this exact record layout, use its name as a type, otherwise you will have to explicitly declare the type:

 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; 

Answer 5

You can achieve this simply by using a set of returned records using a return request.

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

Answer 6

you can do this using the 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)