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?

Answer 1

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