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

2008/10/13 Vladimir Dzhuvinov <vd(at)valan(dot)net>:
> 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
>
>