Re: [RFC] Set Returning Functions

> Do we want this feature?
> -----------------------------------------------------
> Based on the many posts on this topic, I think the answer to this is a
> resounding yes.

Definitely!

> How do we want the feature to behave?
> -----------------------------------------------------
> A SRF should behave similarly to any other table_ref (RangeTblEntry),
> i.e. as a tuple source in a FROM clause. Currently there are three
> primary kinds of RangeTblEntry: RTE_RELATION (ordinary relation),
> RTE_SUBQUERY (subquery in FROM), and RTE_JOIN (join). SRF would join
> this list and behave in much the same manner.

Yes - I don't see any point in adhering to the SQL standard lame definition.
We can just make "CALL proc()" map to "SELECT * FROM proc()" in the parser
for compliance.

> How do we want the feature implemented? (my proposal)
> -----------------------------------------------------
> 1. Add a new table_ref node type:
> - Current nodes are RangeVar, RangeSubselect, or JoinExpr
> - Add new RangePortal node as a possible table_ref. The RangePortal
> node will be extented from the current Portal functionality.
>
> 2. Add support for three modes of operation to RangePortal:
> a. Repeated calls -- this is the existing API for SRF, but
> implemented as a tuple source instead of as an expression.
> b. Materialized results -- use a TupleStore to materialize the
> result set.
> c. Return query -- use current Portal functionality, fetch entire
> result set.
>
> 3. Add support to allow the RangePortal to materialize modes 1 and 3, if
> needed for a re-read.

Looks cool. That's stuff outta my league tho.

> 4. Add a WITH keyword to CREATE FUNCTION, allowing SRF mode to be
> specified. This would default to mode a) for backward compatibility.

Interesting idea. Didn't occur to me that we could specify it on a
per-function level. How do Oracle and Firebird do it? What about the issue
of people maybe wanting different behaviours at different times? ie.
statement level, rather than function level?

> 5. Ignore the current code which allows functions to return multiple
> results as expressions; we can leave it there, but deprecate it with the
> intention of eventual removal.

What does the current 'setof' pl/pgsql business actually _do_?

Chris