Why is there a difference in behavior between using a Set Returning Function (SRF) in the SELECT list vs using SRF in the FROM clause?
For example, for a simple SRF returning 2 rows:
CREATE OR REPLACE FUNCTION gen_series(out integer, out int) RETURNS SETOF record AS $$ SELECT 1,1 UNION SELECT 2,2; $$ LANGUAGE SQL;
SELECT gen_series(); returns two single column rows each containing a record:
=> gen_series ------------ (1,1) (2,2) (2 rows)
SELECT * FROM gen_series(); returns two rows with the record expanded:
=> column1 | column2 ---------+--------- 1 | 1 2 | 2 (2 rows)
By comparison, if the SRF is returning a single column, then calling the SRF in the SELECT or FROM clause makes no difference. e.g.:
=> SELECT generate_series(1,2); generate_series ----------------- 1 2 (2 rows) => SELECT * FROM generate_series(1,2); generate_series ----------------- 1 2 (2 rows)
My questions are:
I don't quite see why in the second case, the SRF behavior is different from the first case just because the returned table has a single column. Is this really consistent behavior in terms of types, tuples and sets?
What's the difference between the two cases that leads to the different behavior?
SRF can be used as tables as shown above, but can tables be used to replace SRFs as well? e.g.
Apparently, this can't be done, but why is
SELECT my_SRF(); possible, whereas
SELECT my_table; is not allowed (in terms of relations and mathematics)?