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@interia.pl> wrote:
>
> The following bug has been logged online:
>
> Bug reference:      3851
> Logged by:          Lunter
> Email address:      lunter@interia.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
> [@var INT]
> AS
> BEGIN
>  SELECT * FROM Table1 WHERE id = @var;
>  SELECT * FROM Table2;
>  SELECT * FROM Table3;
> END
>
> ---
>
> EXEC/CALL procedure_name [@var = 10]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>