8

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

  • SELECT my_table; is not a valid syntax – Mladen Uzelac May 2 '17 at 10:15
3

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 the 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 when you combine multiple SRF functions in the SELECT list. This is going to change with Postgres 10 for good, though:

The safe, less confusing way for either case is to move SRF functions to the FROM clause. Use a LATERAL join if you need to refer to columns from another table. The manual suggests:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.