Tom Lane wrote:
>>3. PL/pgSQL support for returning sets -- this seems to me like an
>>important item if SRFs are to be useful to the masses. Any pointers on
>>how to approach this would be appreciated.
>
> Does Oracle's pl/sql support this? If so what does it look like?
I *think* Oracle pl/sql can return (the equivilent of) setof composite
using a special Oracle package (DBMS_OUTPUT, see:
http://www.ora.com/catalog/oraclebip/chapter/ch06.html), but it cannot
be used as a row source in a FROM clause. Hopefully an Oracle guru will
correct or add to this.
I know that MS SQL Server can return one *or more* result sets from a
"stored procedure", however they cannot be used as FROM clause row
sources either (at least not as of MSSQL 7, but I don't think that has
changed in MSSQL 2000). The syntax is something like: exec sp_myprocedure
It is *not* possible to define a VIEW based on a stored procedure, but
many MS centric report writers allow the "exec sp_myprocedure" syntax as
a row source for reports.
As far as PL/pgSQL is concerned, I was thinking that a new type of
RETURN (maybe "RETURN NEXT myval" ??) command could be used, which would
indicate "rsi->isDone = ExprMultipleResult", and that the standard
RETURN command would set "rsi->isDone = ExprEndResult", but only if
"fcinfo->resultinfo != NULL". That way you could do something like:
. . .
FOR row IN select_query LOOP statements RETURN NEXT row;
END LOOP;
RETURN NULL;
. . .
Does this sound reasonable?
Joe
