Return multiple fields as a record in PostgreSQL with PL/pgSQL
Solution 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) /* , ... */ )
...
Solution 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.
Solution 3
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 ofOUT
parameters combined withRETURNS SETOF record
, just shorter.The major difference: this function can return 0, 1 or many rows, while the first version always returns 1 row.
AddLIMIT 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:
Solution 4
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;
Solution 5
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);
Related videos on Youtube
skyeagle
Updated on December 07, 2021Comments
-
skyeagle over 2 years
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
-
nate c over 13 yearsDid investigate returning multiple records e.g.
returns setof my_type
-
skyeagle over 13 years@nate: its not the function that returns a set. I need to fetch records WITHIN the SP, and then retrieve fields from INDIVIDUAL records in the retrieved set, in the data returned from the function - sounnds more complicated than it actually is - see the code above
-
-
skyeagle over 13 yearsPlease see the modified question - it turns out the original question was too simplified for my actual needs. Thanks
-
a_horse_with_no_name over 13 yearsI'm not sure I understand the "new" problem, but it all boils down to finding the correct queries that retrieve the desired result. Once you have them, returning the result should not be a problem
-
skyeagle over 13 yearsThe new problem is that I need to be able to offset into retrieved rows and fetch specific fields from the retrieved set, so that I can populate the 'flattened' structure that is returned from the SP. The new problem boils down to the following two questions: (1). what data type do I use to receive a set of rows from a query (2)> How do I access field "f1" of the Nth row in the returned set?
-
a_horse_with_no_name over 13 yearsI would suggest to open a new question for this. In a nutshell: for 1) you would use a cursor to iterate over the result and pick the ones you need, for 2) you keep a counter e.g. using row_number() that identifies the specific row. Or even better: only select that row. But all that will not change the signature of your function. It is enough to return the type you have specified.
-
Sean over 12 yearsDon't use a new
TYPE
to solve this problem. Just use aRECORD
and alias the members in theRECORD
. See stackoverflow.com/questions/4547672/… for a more correct answer that doesn't involve customTYPE
s. -
alfonx about 12 yearsIndeed handy.. I was wondering how to split this record again... Thanks!
-
SabreWolfy almost 12 years@Sean: Great answer thanks -- I'm going to try this. And splitting out the results in the last example is very cool.
-
SabreWolfy almost 12 years@Sean: Thanks. I find the
SELECT a, b, c ...
very useful. How could I use that method with something like:SELECT code, theFunction(code) from theTable ;
, where I amSELECT
ing from the table rather than the function? -
Sean almost 12 years@SabreWolfy, you can't to the best of my knowledge unless you make
theFunction
a set-returning function. -
Satish Sharma over 11 years@Sean can u please let me know that what is the way to return multiple rows from RECORD type ??
-
Sean over 11 years@SatishSharma Use a sub-SELECT and the crosstab contrib function. postgresql.org/docs/current/static/tablefunc.html
-
Erwin Brandstetter about 11 years
RETURNS record
is a measure of last resort. It forces you to provide the column definition list with every call, which is very unwieldy. There are almost always better ways. Also: There are ways to have actual polymorphic results with polymorphic types. -
Sean about 11 yearsAbsolutely it can be unwieldy, but
EXECUTE
forces the contents of theEXECUTE
command to be re-planned every time, whereas normal SQL statements in pl/PgSQL will cache the query plan. There's certainly a tradeoff between the two approaches to solve this. I prefer the explicit and more unwieldy approach over dynamic SQL construction and execution, especially inSECURITY DEFINER
contexts. -
Erwin Brandstetter about 10 years@Sean: 1. Since Postgres 9.2, plain SQL statements are re-planned, too. Details here. 2. The return type is independent of
EXECUTE
or not. 3. The technique in the answer only works for single-row results not forSETOF record
. 4. If your return type is constant anyway (like in your example), use a well known type. The idea with an anonymous record only makes sense in rare cases with varying return types. -
Sean about 10 years@ErwinBrandstetter 1) Yes, 9.2 hadn't been released when I wrote this answer. 4) I've found it particularly useful as a way of returning multiple columns worth of information from a function. It's not a universal solution, but returning multiple typed and named elements from a function is nice.
-
Yaki Klein almost 9 yearsWhat will happen in a case where the query has 0 results? The function will still return one row (ret RECORD ) even though its expected to return 0 rows.
-
Yaki Klein almost 9 yearsWhat will happen in a case where the query has 0 results? The function will still return one row (result_record my_type ) with null values all though its expected to return 0 rows
-
bearrito over 8 yearsAs of 9.4 the correct syntax requires the 'AS' modifier, so the example above would be : CREATE TYPE my_type AS (f1 varchar(10), f2 varchar(10), ... );
-
Raiden Core about 7 yearsThis is wrong, don't fall into this trap, it works as he says, but this makes the code integrity depends on columns order of the table, this MUST not be tolerated because eventually the column order of the table will change and highly likely would be different between development and deployment.
-
lucid_dreamer almost 7 years@Raiden Why would the column order "eventually" (when?) change?
-
Raiden Core almost 7 yearsYou are not planning to make your development machine the same as your production machine, in PostgreSQL all mirroring solutions are forced to change the column order, not their fault, this is how PostgreSQL is made, you can not even change the column order in commands, this unique to postgresql
-
Eugen Konkov almost 5 yearsWould be nice if this possible:
RETURNS TABLE( auto )
which will not require to usefrom my_fn() as ( field type, field, type )
as forRETURNS SETOF record
-
marcellothearcane about 4 yearsI get
the function does not return a "COMPOSITE" type • the function does not return a SETOF table
when trying this with Hasura. -
Erwin Brandstetter about 4 years@marcellothearcane: The functions work as advertised. See the added fiddle.
-
marcellothearcane about 4 yearsThanks, it's a problem with Hasura I think - I'm returning
SETOF <table>
which seems to work!