Search Postgresql Archives

Re: PL/pgSQL stored procedure returning multiple result sets (SELECTs)?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



2008/10/13 Vladimir Dzhuvinov <vd@xxxxxxxxx>:
> Hi,
>
> I've got a financial MySQL database where the application accesses data
> through a layer of stored procedures. For various reasons I'm currently
> investigating my options to migrate to another SQL RDBMS.
>
> Postgresql seems to offer a few nice advantages over MySQL (e.g.
> stricter data integrity through checks and constraints, etc.) and I got
> quite excited about it.
>
> However, after consulting the docs and running a few tests, it looks
> like Postgresql misses a crucial feature which my application depends
> upon - returning multiple SELECT result sets from functions/stored
> procedures.
>
> To illustrate, I've got a number of MySQL stored procedures that look
> approximately like this:
>
> CREATE PROCEDURE list_user_accounts(IN user_id INT)
>
>        BEGIN
>
>        -- Return first result set (single row)
>        SELECT * FROM users WHERE id = user_id;
>
>        -- Return second result set (zero or more rows)
>        SELECT * FROM accounts WHERE account_holder = user_id;
>
>        END;
>
>
> So, is it true that as of Postgresql 8.3 there is no way to have a
> pgpqsql function return multiple SELECTs?

Hello,

it's true. You can use setof cursors instead.

http://www.postgresql.org/docs/8.1/static/plpgsql-cursors.html

regards
Pavel Stehule
>
>
> Vladimir Dzhuvinov
>
> --
> Vladimir Dzhuvinov * www.valan.net * PGP key ID AC9A5C6C
>
>

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux