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

From: Vladimir Dzhuvinov <vd(at)valan(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
Date: 2008-10-13 09:17:21
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general


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

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)


-- 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;


So, is it true that as of Postgresql 8.3 there is no way to have a
pgpqsql function return multiple SELECTs?

Vladimir Dzhuvinov

Vladimir Dzhuvinov * * PGP key ID AC9A5C6C


Browse pgsql-general by date

  From Date Subject
Next Message Grzegorz Jaśkiewicz 2008-10-13 09:19:16 Re: Drupal and PostgreSQL - performance issues?
Previous Message admin 2008-10-13 09:08:24 Re: Drupal and PostgreSQL - performance issues?