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

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]

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
>