Parameterize set-returning function with column in the same query
Essentially, what I want to do is:
SELECT set_returning_func(id) FROM my_table;
However, the result will be a single column in record syntax, e.g.
set_returning_func
---------------------------------------------
(old,17,"August 2, 201开发者_高级运维1 at 02:54:59 PM")
(old,28,"August 4, 2011 at 08:03:12 AM")
(2 rows)
I want it to be unpacked into columns. If I write the query this way instead:
SELECT srf.* FROM my_table, set_returning_func(my_table.id);
I get an error message:
ERROR: function expression in FROM cannot refer to other relations of same query level
How, then, do I get a result set, while also supplying the set-returning function with an argument?
The syntax I was looking for is:
SELECT (set_returning_func(id)).* FROM my_table;
Details
set_returning_func(id)
is of composite type. Just as the *
syntax can be used on tables:
SELECT my_table.* FROM my_table, my_other_table
It can also be used on composite values (though they must be wrapped in parentheses). Intuitively, one can also select individual columns from a composite-returning function:
SELECT (set_returning_func(id)).time FROM my_table;
Some set-returning functions have a scalar rather than composite return type. In these cases, the (expr).*
syntax doesn't make sense, and produces an error:
> SELECT (generate_series(1,5)).*;
ERROR: type integer is not composite
The correct syntax is simply:
> SELECT generate_series(1,5);
generate_series
-----------------
1
2
3
4
5
(5 rows)
精彩评论