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