PostgreSQL function or stored procedure that outputs multiple columns?

Question

Here is what I ideally want. Imagine that I have a table with the row A.

I want to do:

SELECT A, func(A) FROM table

and for the output to have say 4 columns.

Is there any way to do this? I have seen things on custom types or whatever that let you sort of get a result that would look like

A,(B,C,D)

But it would be really great if I could have that one function return multiple columns without any more finagling.

Is there anything that can do something like this?

bambam · Answer 1 · 2011-05-07T17:24:50.267

If the function func returns only 1 row with 3 values, such as:

CREATE OR REPLACE FUNCTION func
(
    input_val       integer,
    OUT output_val1 integer,
    OUT output_val2 integer,
    OUT output_val3 integer
)
AS $$
BEGIN
  output_val1 := input_val + 1;
  output_val2 := input_val + 2;
  output_val3 := input_val + 3;
END;
$$ LANGUAGE plpgsql;

and you then execute SELECT a, func(a) FROM table1 you'll get:

a       | func
integer | record
========|==========
1       | (2, 3, 4)
2       | (3, 4, 5)
3       | (4, 5, 6)

but, if you execute:

SELECT a, (f).output_val1, (f).output_val2, (f).output_val3
FROM (SELECT a, func(a) AS f FROM table1) AS x

you'll get:

a       | output_val1 | output_val2 | output_val3
integer | integer     | integer     | integer
========|=============|=============|=============
1       | 2           | 3           | 4
2       | 3           | 4           | 5
3       | 4           | 5           | 6

or, using CTE (Common Table Expressions), if you execute:

WITH temp AS (SELECT a, func(a) AS f FROM table1)
SELECT a, (f).output_val1, (f).output_val2, (f).output_val3 FROM temp

you'll also get:

a       | output_val1 | output_val2 | output_val3
integer | integer     | integer     | integer
========|=============|=============|=============
1       | 2           | 3           | 4
2       | 3           | 4           | 5
3       | 4           | 5           | 6

Note: you may also use the following queries to obtain the same results:

SELECT a, (f).*
FROM (SELECT a, func(a) AS f FROM table1) AS x

or

WITH temp AS (SELECT a, func(a) AS f FROM table1)
SELECT a, (f).* FROM temp

or `SELECT a, (func(a)).* FROM table1`. postgres is insane - I love it. +1 for you +100 for postgres — , May 07 '11 at 18:15

score 9 · Answer 2 · answered Jun 02 '11 at 20:24

9

I agree with bambam's answer but would like to point out that JackPDouglas's more succinct syntax SELECT a, (func(a)).* FROM table1, from my tests, would actually execute the function once for each column returned whereas the CTE expression will only execute the function once. So the CTE expression is preferred if the function takes a long time to execute.

answered Jun 02 '11 at 20:24

ClubCranium

111
1
4

2

+1 although worth pointing out that if the function is `IMMUTABLE` or `STABLE` it wouldn't matter very much either way – Jul 11 '11 at 13:07

rudi-moore · Answer 3 · 2011-03-29T18:43:05.123

2

If the function always returns 3 columns, you can do something like that:

CREATE TYPE sometype AS (b INT, c TEXT, d TEXT);

CREATE OR REPLACE FUNCTION func(a TEXT) RETURNS SETOF sometype AS $$
BEGIN
  RETURN QUERY EXECUTE 'SELECT b, c, d FROM ' || a;
END;
$$ LANGUAGE plpgsql;

SELECT a, (f).b, (f).c, (f).d 
FROM (SELECT a, func(a) AS f FROM table) x;

If you can access the table from within a view, maybe you can create a view in some way

CREATE VIEW v AS 
SELECT 'tab1' AS a, b, c, d FROM tab1 WHERE 'tab1' IN (SELECT a FROM table)
UNION
SELECT 'tab2' AS a, b, c, d FROM tab2 WHERE 'tab2' IN (SELECT a FROM table)
UNION
SELECT 'tab3' AS a, b, c, d FROM tab3 WHERE 'tab3' IN (SELECT a FROM table);

then it's just a SELECT * FROM v. But again this looks like Inheritance could be used.

edited Mar 29 '11 at 18:43

answered Mar 29 '11 at 12:20

rudi-moore

2,650
1
19
16

The problem I have with this is that it requires some pretty clunky handling from the users. I understand if it is the only option I have, but I am trying to make things MORE elegant for the users, not less. ideally I'd want them to be able to do SELECT myfunc(); and it would output a complete table as specified by me, and then go from there, perhaps with some flags and whatnot. Maybe SQL just straight up cannot do this? – A Question Asker Mar 29 '11 at 14:14
Your users execute SQL directly? I don't really understand the purpose of that. The user will input a tablename and some flags and get different number of columns and different type of columns for every tablename? Then what will they do with this. They have to write different statements for every table too or not? So what is the advantage for the user to write `SELECT myfunc('tablename')` instead of writing `SELECT * FROM tablename`. Or do all your tables have a similar table definition? Then you maybe want Inheritance http://www.postgresql.org/docs/9.0/interactive/ddl-inherit.html – rudi-moore Mar 29 '11 at 16:08
Perhaps I commented incorrectly. I guess what I mean to say is that I'd like to be able to compress what you wrote, which is cool, into one line of the form SELECT a, f(a) FROM table; you'll get 4 columns. So basically the same thing that you have, just compressed into one line. Yours is definitely good to know though...just wondering why we can't make it even cleaner? – A Question Asker Mar 29 '11 at 17:50
`SELECT a, f(a) FROM table` defines only two result columns. So you will never get 4 columns. You can maybe do something like that `SELECT a,b,c,d FROM f((SELECT a FROM v))` where v is a view `CREATE VIEW v AS SELECT array(SELECT a FROM table) a`. But don't know if that is better and in that case the function must take a array, execute a query for every member of the array and return all the inputed value and the 3 columns `CREATE FUNCTION f(IN a_in TEXT[], OUT a TEXT, OUT b INT, OUT c TEXT, OUT d TEXT) RETURNS SETOF record AS $$ ..`. – rudi-moore Mar 29 '11 at 18:13

score 0 · Answer 4 · answered Mar 28 '11 at 22:11

I think you will want to return a single record, with multiple columns? In that case you can use the return-type RECORD for example. This will allow you to return an anonymous variable with as many columns as you want. You can find more information about all the different variables here:

http://www.postgresql.org/docs/9.0/static/plpgsql-declarations.html

And about return types:

http://www.postgresql.org/docs/9.0/static/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS

If you want to return multiple records with multiple columns, first check and see if you have to use a stored procedure for this. It might be an option to just use a VIEW (and query it with a WHERE-clause) instead. If that's not a good option, there is the possibility of returning a TABLE from a stored procedure in version 9.0.

I thought that returning a table could be a good option, but given my use it is a bit awkward. I mean my goal is to be able to allow the user to do something like: SELECT col_a, col_b, func(col_a) FROM a_table; and the result would be a table: col_a, col_b, func_col_a, func_col_b, func_col_c. I am basically trying to wrap complicated functionality into functions without forcing users to use a bunch of really hideous syntax, as in the answer below. The reason why this solution is also necessary is because I often will have to use LIMITs, so nested stuff only gets worse. Is what I want possibl? — A Question Asker, Mar 29 '11 at 14:15

PostgreSQL function or stored procedure that outputs multiple columns?

4 Answers4