Understanding Set Returning Function (SRF) in the SELECT List

Question

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)

Whereas 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:

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

2. What's the difference between the two cases that leads to the different behavior?

3. SRF can be used as tables as shown above, but can tables be used to replace SRFs as well? e.g.

   SELECT my_table; 
   

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

Answer 1

Postgres treats the simple case differently. Multiple columns are treated as composite type (table row), which is only decomposed with SELECT * FROM ..., while a single column of scalar type is treated as just that, no added composite type wrapper. So SELECT my_SRF() produces the same as SELECT * FROM my_SRF() for that simple case. The manual about Table Functions:

Table functions are functions that produce a set of rows, made up of either base data types (scalar types) or composite data types (table rows).

I agree this is confusing, and you are not the first to be confused. (Consider the alternative, though: adding a composite type wrapper around a single column might be even more confusing.)

But not as confusing as what happens used to happen with multiple SRF functions in the SELECT list before Postgres 10. This was fixed for good:

• What is the expected behaviour for multiple set-returning functions in select clause?

The standard SQL way is to move SRF functions to the FROM clause. Use a LATERAL join to operate on table columns. The manual suggests:

The LATERAL syntax produces less surprising results when calling multiple set-returning functions, and should usually be used instead.

• What is the difference between LATERAL and a subquery in PostgreSQL?