Skip site navigation (1) Skip section navigation (2)

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
Message-ID: 48F31221.2020802@valan.net (view raw or flat)
Thread:
Lists: pgsql-general
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?


Vladimir Dzhuvinov

-- 
Vladimir Dzhuvinov * www.valan.net * PGP key ID AC9A5C6C

Responses

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group