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
Show quoted text
--
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
Hi Ivan,
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?
Yes, the client library will have to support multiple result sets too.
For example, the PHP MySQLi lib does that by providing functions to
check for and retrieve outstanding result sets in the buffer:
bool mysqli_more_results ($link)
bool mysqli_next_result ($link)
It seems like the PHP PG binding does allow (?) retrieval of multiple
result sets through pg_get_result(), but only for requests issued
asynchronously:
http://bg2.php.net/manual/en/function.pg-get-result.php
Out of curiosity, what language are you using?
For MySQL I've been mostly using PHP, occasionally Java, Python and C.
Vladimir
--
Vladimir Dzhuvinov * www.valan.net * PGP key ID AC9A5C6C
Import Notes
Resolved by subject fallback
On Mon, 13 Oct 2008 16:48:33 +0300
Vladimir Dzhuvinov <vd@valan.net> wrote:
It seems like the PHP PG binding does allow (?) retrieval of
multiple result sets through pg_get_result(), but only for
requests issued asynchronously:
Interesting.
Out of curiosity, what language are you using?
For MySQL I've been mostly using PHP, occasionally Java, Python
and C.
pardon the noise I forgot to check mysql*i* functions.
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
Vladimir Dzhuvinov wrote:
-- Start of PGP signed section.
Hi Ivan,
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?Yes, the client library will have to support multiple result sets too.
For example, the PHP MySQLi lib does that by providing functions to
check for and retrieve outstanding result sets in the buffer:bool mysqli_more_results ($link)
bool mysqli_next_result ($link)
It seems like the PHP PG binding does allow (?) retrieval of multiple
result sets through pg_get_result(), but only for requests issued
asynchronously:http://bg2.php.net/manual/en/function.pg-get-result.php
Out of curiosity, what language are you using?
For MySQL I've been mostly using PHP, occasionally Java, Python and C.
Interesting. I think we need to decide if we want a TODO for this.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Hello
2008/10/15 Bruce Momjian <bruce@momjian.us>:
Vladimir Dzhuvinov wrote:
-- Start of PGP signed section.Hi Ivan,
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?Yes, the client library will have to support multiple result sets too.
For example, the PHP MySQLi lib does that by providing functions to
check for and retrieve outstanding result sets in the buffer:bool mysqli_more_results ($link)
bool mysqli_next_result ($link)
It seems like the PHP PG binding does allow (?) retrieval of multiple
result sets through pg_get_result(), but only for requests issued
asynchronously:http://bg2.php.net/manual/en/function.pg-get-result.php
Out of curiosity, what language are you using?
For MySQL I've been mostly using PHP, occasionally Java, Python and C.
Interesting. I think we need to decide if we want a TODO for this.
year ago I wrote prototype:
http://okbob.blogspot.com/2007/11/stacked-recordset-multirecordset.html
http://okbob.blogspot.com/2007/11/first-real-procedures-on-postgresql.html
regards
Pavel Stehule
Show quoted text
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com+ If your life is a hard drive, Christ can be your backup. +
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Added to TODO:
Add support for returning multiple result sets?
* http://archives.postgresql.org/pgsql-general/2008-10/msg00454.php
---------------------------------------------------------------------------
Pavel Stehule wrote:
Hello
2008/10/15 Bruce Momjian <bruce@momjian.us>:
Vladimir Dzhuvinov wrote:
-- Start of PGP signed section.Hi Ivan,
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?Yes, the client library will have to support multiple result sets too.
For example, the PHP MySQLi lib does that by providing functions to
check for and retrieve outstanding result sets in the buffer:bool mysqli_more_results ($link)
bool mysqli_next_result ($link)
It seems like the PHP PG binding does allow (?) retrieval of multiple
result sets through pg_get_result(), but only for requests issued
asynchronously:http://bg2.php.net/manual/en/function.pg-get-result.php
Out of curiosity, what language are you using?
For MySQL I've been mostly using PHP, occasionally Java, Python and C.
Interesting. I think we need to decide if we want a TODO for this.
year ago I wrote prototype:
http://okbob.blogspot.com/2007/11/stacked-recordset-multirecordset.html
http://okbob.blogspot.com/2007/11/first-real-procedures-on-postgresql.htmlregards
Pavel Stehule--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com+ If your life is a hard drive, Christ can be your backup. +
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +