Thread: multi recordset and data type check was: Re: PL/pgSQL stored procedure returning multiple result sets (SELECTs)?

>> 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;
>
> I'd say returning multiple recordset is useful to save connections
> and transferred data.
> You can't get the same with a left join (users fields will be
> repeated over and over) and you can't get the same with 2 separated
> statements since they will need 2 connections.
>
> But from the client side, suppose it PHP... if the first
> statement return no record and the second one return 3 records, how
> can I know?

Well, (in MySQL at least) in that case you're still going to get a
result set, it's just going to be an empty one (result with no rows).

So, no matter how many rows the SELECT statements resolve to, you're
always going to get two result sets :)


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

On Mon, 13 Oct 2008 15:19:33 +0300
Vladimir Dzhuvinov <vd@valan.net> wrote:

> Well, (in MySQL at least) in that case you're still going to get a
> result set, it's just going to be an empty one (result with no
> rows).

> So, no matter how many rows the SELECT statements resolve to,
> you're always going to get two result sets :)

It seems anyway that the usefulness of this feature largely depends
on the language library.
eg. I can't see a way to support it with php right now but it is
supported by python.
Am I missing something?

Out of curiosity, what language are you using?

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

Hello

2008/10/13 Ivan Sergio Borgonovo <mail@webthatworks.it>:
> On Mon, 13 Oct 2008 15:19:33 +0300
> Vladimir Dzhuvinov <vd@valan.net> wrote:
>
>> Well, (in MySQL at least) in that case you're still going to get a
>> result set, it's just going to be an empty one (result with no
>> rows).
>
>> So, no matter how many rows the SELECT statements resolve to,
>> you're always going to get two result sets :)
>
> It seems anyway that the usefulness of this feature largely depends
> on the language library.
> eg. I can't see a way to support it with php right now but it is
> supported by python.
> Am I missing something?
>
> Out of curiosity, what language are you using?

I know so multirecordsets are well supported for php and MySQL, and in
all Microsoft environments - Microsoft SQL Server use it very hard.
These functionality has lot of advantage, mainly in stateless
environment like plpgsql.

regards
Pavel Stehule

>
> --
> Ivan Sergio Borgonovo
> http://www.webthatworks.it
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>