Re: Multiple SRF right after SELECT

On Wed, 19 Mar 2008, "Nikolay Samokhvalov" <samokhvalov(at)gmail(dot)com> writes:
> I wonder, if the following is correct and provides expected result:
>
> test=# select generate_series(1, 2), generate_series(1, 4);
> generate_series | generate_series
> -----------------+-----------------
> 1 | 1
> 2 | 2
> 1 | 3
> 2 | 4
> (4 rows)
>
>
> 1. Is it correct at all to use SRF in select list, w/o explicit FROM?
> Why then we do not allow using subselects that return multiple rows?
> I'd rather expect that these two things work in similar manner.
> 2. Why the query above provides 4 rows, not 2*4=8? Actually, that's
> interesting -- I can use this query to find l.c.m. But it's defenetely
> not that I'd expect before my try...

From PL/scheme sources:

/*
* There're 2 ways to return from an SRF:
*
* 1. Value-per-call Mode
* You return each tuple one by one via SRF_RETURN_NEXT() macro. But
* PG_RETURN_DATUM() calls in the macro, makes it quite
* impracticble. OTOH, this method gives opportunity to call SRFs in
* a fashion like "SELECT mysrf();"
*
* 2. Materialize Mode
* In this mode, you collect all tuples in a single set and return
* that set. When compared to previos method, it's not possible to
* use SRF of materialize mode like "SELECT my_materialized_srf();",
* instead, you need to access it as a simple table: "SELECT * FROM
* my_materialized_srf();".
*
* ...
*/

And I conclude to that generate_series() is written as a SRF function of
value-per-call mode. (Also you may want to check Returning Sets[1]
chapter at PostgreSQL manual.)

[1] http://www.postgresql.org/docs/current/static/xfunc-c.html#XFUNC-C-RETURN-SET

Regards.