Re: BUG #3851: suggestion - support for stored procedures

Hello

use SETOF cursors.

CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
BEGIN
OPEN $1 FOR SELECT * FROM table_1;
RETURN NEXT $1;
OPEN $2 FOR SELECT * FROM table_2;
RETURN NEXT $2;
END;
$$ LANGUAGE plpgsql;

-- need to be in a transaction to use cursors.
BEGIN;

SELECT * FROM myfunc('a', 'b');

FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;

http://www.postgresql.org/docs/8.2/interactive/plpgsql-cursors.html

Regards
Pavel Stehule

On 05/01/2008, Lunter <lunter(at)interia(dot)pl> wrote:
>
> The following bug has been logged online:
>
> Bug reference: 3851
> Logged by: Lunter
> Email address: lunter(at)interia(dot)pl
> PostgreSQL version: 9.0 ?
> Operating system: any
> Description: suggestion - support for stored procedures
> Details:
>
> Some database servers support stored procedures that return more than one
> rowset (also known as a result set).
> It is very usefull and it permit to full separate SQL statement from
> scripting language code and make possible return more than one rowset on one
> calling to database.
>
> CREATE PROC procedure_name
> [(at)var INT]
> AS
> BEGIN
> SELECT * FROM Table1 WHERE id = @var;
> SELECT * FROM Table2;
> SELECT * FROM Table3;
> END
>
> ---
>
> EXEC/CALL procedure_name [(at)var = 10]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>