From: | Joe Conway <mail(at)joeconway(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers <pgsql-hackers(at)postgreSQL(dot)org> |
Subject: | Re: SRF rescan testing |
Date: | 2002-05-26 16:55:06 |
Message-ID: | 3CF1136A.6090904@joeconway.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-patches |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Olivier PRENANT | 2002-05-26 16:55:21 | WAL FILES |
Previous Message | Tom Lane | 2002-05-26 15:58:51 | Re: Q: unexpected result from SRF in SQL |
From | Date | Subject | |
---|---|---|---|
Next Message | Joe Conway | 2002-05-27 01:30:32 | revised sample SRF C function; proposed SRF API |
Previous Message | Bear Giles | 2002-05-25 08:20:00 | SSL (patch 10) |