Re: [HACKERS] returning multiple result sets from a stored procedure
Pavel Stehule wrote: 2010/9/9 Tom Lane t...@sss.pgh.pa.us: Darren Duncan dar...@darrenduncan.net writes: Since Pg's FUNCTION already seems to take on both roles, so overloading the meaning of the FUNCTION keyword, like what a C function or a Perl sub does, where returning VOID means procedure, then what is being added by a distinct PROCEDURE? You might care to go back and re-read some of the extensive prior threads about this, but to my mind the main thing that would justify inventing a separate PROCEDURE facility is if procedures were to execute outside the transaction system, so that they could start and stop transactions for themselves. ?This is unlike a function which necessarily executes inside an already-running transaction. ?Of course a lot of questions would need to be answered about error-handling behavior and so forth, but that's a capability that a LOT of people have asked for. it's only one request from two mayor request * transaction handling * unbound SELECTs and multirecordset support and some more classic handling of OUT variables. I assume the current thought is that our functions would remain unchanged and new procedures would allow either of these. I have updated the procedure todo item to read: Implement stored procedures This might involve the control of transaction state and the return of multiple result sets * PL/pgSQL stored procedure returning multiple result sets (SELECTs)? * Proposal: real procedures again (8.4) * http://archives.postgresql.org/pgsql-hackers/2010-09/msg00542.php -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
OK, how much are we talking about? From: Josh Berkus j...@agliodbs.com To: John Adams john_adams_m...@yahoo.com Cc: PostgreSQL-development pgsql-hackers@postgreSQL.org Sent: Fri, September 3, 2010 1:07:03 PM Subject: Re: [HACKERS] returning multiple result sets from a stored procedure I noticed in postgres you cannot return multiple result sets from a stored procedure (surprisingly as it looks like a very good dbms). That feature has been on the TODO list for years. However, nobody has stepped forward to either write it, or to fund working on it. If your company has programmers or money to build this feature, it could probably get done fairly quickly (as in, next version). -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
Hello 2010/9/9 John Adams john_adams_m...@yahoo.com: OK, how much are we talking about? about 2 months for full time and 2 months for partial time - is my tip depends on set of implemented features regards Pavel Stehule From: Josh Berkus j...@agliodbs.com To: John Adams john_adams_m...@yahoo.com Cc: PostgreSQL-development pgsql-hackers@postgreSQL.org Sent: Fri, September 3, 2010 1:07:03 PM Subject: Re: [HACKERS] returning multiple result sets from a stored procedure I noticed in postgres you cannot return multiple result sets from a stored procedure (surprisingly as it looks like a very good dbms). That feature has been on the TODO list for years. However, nobody has stepped forward to either write it, or to fund working on it. If your company has programmers or money to build this feature, it could probably get done fairly quickly (as in, next version). -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
On Sep 9, 2010, at 12:12 AM, Pavel Stehule wrote: about 2 months for full time and 2 months for partial time - is my tip Two months full or two months partial? I'll take the latter, please! David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
2010/9/9 David E. Wheeler da...@kineticode.com: On Sep 9, 2010, at 12:12 AM, Pavel Stehule wrote: about 2 months for full time and 2 months for partial time - is my tip Two months full or two months partial? I'll take the latter, please! 2 months - basic implementation 1 months - cleaning and work on commiting sum - 3 month Regards Pavel p.s. I am working on basic syntax - CALL and OUT variables. But I'll not try to implement a transaction related features. Regards Pavel Stehule Two months full for basic implementation and two months partial David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
Pavel Stehule pavel.steh...@gmail.com writes: 2010/9/9 David E. Wheeler da...@kineticode.com: On Sep 9, 2010, at 12:12 AM, Pavel Stehule wrote: about 2 months for full time and 2 months for partial time - is my tip Two months full or two months partial? I'll take the latter, please! 2 months - basic implementation 1 months - cleaning and work on commiting sum - 3 month And zero time spent on convincing -hackers that the design is good? Not likely to get committed that way. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
2010/9/9 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: 2010/9/9 David E. Wheeler da...@kineticode.com: On Sep 9, 2010, at 12:12 AM, Pavel Stehule wrote: about 2 months for full time and 2 months for partial time - is my tip Two months full or two months partial? I'll take the latter, please! 2 months - basic implementation 1 months - cleaning and work on commiting sum - 3 month And zero time spent on convincing -hackers that the design is good? Not likely to get committed that way. then I have to add 6 months more :) regards Pavel Stehule regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
2010/9/9 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: 2010/9/9 David E. Wheeler da...@kineticode.com: On Sep 9, 2010, at 12:12 AM, Pavel Stehule wrote: about 2 months for full time and 2 months for partial time - is my tip Two months full or two months partial? I'll take the latter, please! 2 months - basic implementation 1 months - cleaning and work on commiting sum - 3 month And zero time spent on convincing -hackers that the design is good? Not likely to get committed that way. there are lot of questions - and I am not sure if procedures implementation can be done in one release cycle. The basic questions: * should be special catalog for procedures or we will use pg_proc? * how can be implemented OUT variables - the original implementation is simple - it's just pointer, but it's not directly possible inside postgres, because we use a MemoryContexts? * how can be implement a CALL statement - as plan statement or as command? * how can be implemented variables inside psql console, if we allows them? * how can be implement an overloading of procedures - can we use for selection OUT variables too? * what is procedure? It's like void function, or it can return status code like procedures in SQL/PSM (DB2)? --- As long years a stored procedures developer, I can say, so just minimal implementation of procedures can help with writing little bit more readable code for functions that return more then one scalar result. But other features can be nice too - explicit transaction control and unbind selects. But these features are killing gun. Regards Pavel Stehule regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
Excerpts from Pavel Stehule's message of jue sep 09 14:29:57 -0400 2010: * how can be implemented OUT variables - the original implementation is simple - it's just pointer, but it's not directly possible inside postgres, because we use a MemoryContexts? Why is this a problem? Just return a bunch of tuplestores, no? -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
2010/9/9 Alvaro Herrera alvhe...@commandprompt.com: Excerpts from Pavel Stehule's message of jue sep 09 14:29:57 -0400 2010: * how can be implemented OUT variables - the original implementation is simple - it's just pointer, but it's not directly possible inside postgres, because we use a MemoryContexts? Why is this a problem? Just return a bunch of tuplestores, no? and what context do you use? And you will pack and unpack tuple when some field will be changed every time? this is an possibility to solve our performance problems with arrays or strings. Regards Pavel Stehule -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
Pavel Stehule wrote: there are lot of questions - and I am not sure if procedures implementation can be done in one release cycle. The basic questions: * should be special catalog for procedures or we will use pg_proc? * how can be implemented OUT variables - the original implementation is simple - it's just pointer, but it's not directly possible inside postgres, because we use a MemoryContexts? * how can be implement a CALL statement - as plan statement or as command? * how can be implemented variables inside psql console, if we allows them? * how can be implement an overloading of procedures - can we use for selection OUT variables too? * what is procedure? It's like void function, or it can return status code like procedures in SQL/PSM (DB2)? --- As long years a stored procedures developer, I can say, so just minimal implementation of procedures can help with writing little bit more readable code for functions that return more then one scalar result. But other features can be nice too - explicit transaction control and unbind selects. But these features are killing gun. I've often considered that the main distinction between a function and a procedure is that the former is intended to be invoked as a value-resulting expression while the latter is intended to be invoked as a non-value-resulting statement. The SQL standard uses separate FUNCTION and PROCEDURE for these. Since Pg's FUNCTION already seems to take on both roles, so overloading the meaning of the FUNCTION keyword, like what a C function or a Perl sub does, where returning VOID means procedure, then what is being added by a distinct PROCEDURE? Or is the VOID-returning FUNCTION going to be deprecated or discouraged at the same time? -- Darren Duncan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
Darren Duncan dar...@darrenduncan.net writes: Since Pg's FUNCTION already seems to take on both roles, so overloading the meaning of the FUNCTION keyword, like what a C function or a Perl sub does, where returning VOID means procedure, then what is being added by a distinct PROCEDURE? You might care to go back and re-read some of the extensive prior threads about this, but to my mind the main thing that would justify inventing a separate PROCEDURE facility is if procedures were to execute outside the transaction system, so that they could start and stop transactions for themselves. This is unlike a function which necessarily executes inside an already-running transaction. Of course a lot of questions would need to be answered about error-handling behavior and so forth, but that's a capability that a LOT of people have asked for. Or is the VOID-returning FUNCTION going to be deprecated or discouraged at the same time? Certainly not. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
2010/9/9 Darren Duncan dar...@darrenduncan.net: Pavel Stehule wrote: there are lot of questions - and I am not sure if procedures implementation can be done in one release cycle. The basic questions: * should be special catalog for procedures or we will use pg_proc? * how can be implemented OUT variables - the original implementation is simple - it's just pointer, but it's not directly possible inside postgres, because we use a MemoryContexts? * how can be implement a CALL statement - as plan statement or as command? * how can be implemented variables inside psql console, if we allows them? * how can be implement an overloading of procedures - can we use for selection OUT variables too? * what is procedure? It's like void function, or it can return status code like procedures in SQL/PSM (DB2)? --- As long years a stored procedures developer, I can say, so just minimal implementation of procedures can help with writing little bit more readable code for functions that return more then one scalar result. But other features can be nice too - explicit transaction control and unbind selects. But these features are killing gun. I've often considered that the main distinction between a function and a procedure is that the former is intended to be invoked as a value-resulting expression while the latter is intended to be invoked as a non-value-resulting statement. The SQL standard uses separate FUNCTION and PROCEDURE for these. Since Pg's FUNCTION already seems to take on both roles, so overloading the meaning of the FUNCTION keyword, like what a C function or a Perl sub does, where returning VOID means procedure, then what is being added by a distinct PROCEDURE? Or is the VOID-returning FUNCTION going to be deprecated or discouraged at the same time? the overloading of function is based only on input parameters - because there are not entered output variables - it is just some record. But overloading of procedures, can be based on input and output variables. so I can to write CREATE PROCEDURE foo(OUT a int) ... and CREATE PROCEDURE foo(OUT a varchar) ... and then when I use a statement CALL is correct procedure selected CALL foo(textvariable) Regards Pavel -- Darren Duncan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
On Thu, Sep 9, 2010 at 4:17 PM, Pavel Stehule pavel.steh...@gmail.com wrote: 2010/9/9 Darren Duncan dar...@darrenduncan.net: Pavel Stehule wrote: there are lot of questions - and I am not sure if procedures implementation can be done in one release cycle. The basic questions: * should be special catalog for procedures or we will use pg_proc? * how can be implemented OUT variables - the original implementation is simple - it's just pointer, but it's not directly possible inside postgres, because we use a MemoryContexts? * how can be implement a CALL statement - as plan statement or as command? * how can be implemented variables inside psql console, if we allows them? * how can be implement an overloading of procedures - can we use for selection OUT variables too? * what is procedure? It's like void function, or it can return status code like procedures in SQL/PSM (DB2)? --- As long years a stored procedures developer, I can say, so just minimal implementation of procedures can help with writing little bit more readable code for functions that return more then one scalar result. But other features can be nice too - explicit transaction control and unbind selects. But these features are killing gun. I've often considered that the main distinction between a function and a procedure is that the former is intended to be invoked as a value-resulting expression while the latter is intended to be invoked as a non-value-resulting statement. The SQL standard uses separate FUNCTION and PROCEDURE for these. Since Pg's FUNCTION already seems to take on both roles, so overloading the meaning of the FUNCTION keyword, like what a C function or a Perl sub does, where returning VOID means procedure, then what is being added by a distinct PROCEDURE? Or is the VOID-returning FUNCTION going to be deprecated or discouraged at the same time? the overloading of function is based only on input parameters - because there are not entered output variables - it is just some record. But overloading of procedures, can be based on input and output variables. so I can to write CREATE PROCEDURE foo(OUT a int) ... and CREATE PROCEDURE foo(OUT a varchar) ... and then when I use a statement CALL is correct procedure selected CALL foo(textvariable) That seems like a lot of complexity for no real benefit, to me. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
2010/9/9 Tom Lane t...@sss.pgh.pa.us: Darren Duncan dar...@darrenduncan.net writes: Since Pg's FUNCTION already seems to take on both roles, so overloading the meaning of the FUNCTION keyword, like what a C function or a Perl sub does, where returning VOID means procedure, then what is being added by a distinct PROCEDURE? You might care to go back and re-read some of the extensive prior threads about this, but to my mind the main thing that would justify inventing a separate PROCEDURE facility is if procedures were to execute outside the transaction system, so that they could start and stop transactions for themselves. This is unlike a function which necessarily executes inside an already-running transaction. Of course a lot of questions would need to be answered about error-handling behavior and so forth, but that's a capability that a LOT of people have asked for. it's only one request from two mayor request * transaction handling * unbound SELECTs and multirecordset support and some more classic handling of OUT variables. Pavel Or is the VOID-returning FUNCTION going to be deprecated or discouraged at the same time? Certainly not. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
Tom Lane t...@sss.pgh.pa.us wrote: to my mind the main thing that would justify inventing a separate PROCEDURE facility is if procedures were to execute outside the transaction system, so that they could start and stop transactions for themselves. That is the biggest distinction in my mind, too. Supporting multiple result sets just as if the queries were run as independent client-side statements would also be very important. I have seen implementations which support, for a single stored procedure, OUT parameters, a RETURN value, and multiple result sets -- all at the same time, as separate things. I haven't reviewed stored procedures in the SQL standard since an early draft proposal years ago, so I don't know what the current state of that is, but if PostgreSQL approaches this, it'd be nice to implement as many of the above as are not in conflict with requirements of the standard. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
2010/9/9 Robert Haas robertmh...@gmail.com: On Thu, Sep 9, 2010 at 4:17 PM, Pavel Stehule pavel.steh...@gmail.com wrote: 2010/9/9 Darren Duncan dar...@darrenduncan.net: Pavel Stehule wrote: there are lot of questions - and I am not sure if procedures implementation can be done in one release cycle. The basic questions: * should be special catalog for procedures or we will use pg_proc? * how can be implemented OUT variables - the original implementation is simple - it's just pointer, but it's not directly possible inside postgres, because we use a MemoryContexts? * how can be implement a CALL statement - as plan statement or as command? * how can be implemented variables inside psql console, if we allows them? * how can be implement an overloading of procedures - can we use for selection OUT variables too? * what is procedure? It's like void function, or it can return status code like procedures in SQL/PSM (DB2)? --- As long years a stored procedures developer, I can say, so just minimal implementation of procedures can help with writing little bit more readable code for functions that return more then one scalar result. But other features can be nice too - explicit transaction control and unbind selects. But these features are killing gun. I've often considered that the main distinction between a function and a procedure is that the former is intended to be invoked as a value-resulting expression while the latter is intended to be invoked as a non-value-resulting statement. The SQL standard uses separate FUNCTION and PROCEDURE for these. Since Pg's FUNCTION already seems to take on both roles, so overloading the meaning of the FUNCTION keyword, like what a C function or a Perl sub does, where returning VOID means procedure, then what is being added by a distinct PROCEDURE? Or is the VOID-returning FUNCTION going to be deprecated or discouraged at the same time? the overloading of function is based only on input parameters - because there are not entered output variables - it is just some record. But overloading of procedures, can be based on input and output variables. so I can to write CREATE PROCEDURE foo(OUT a int) ... and CREATE PROCEDURE foo(OUT a varchar) ... and then when I use a statement CALL is correct procedure selected CALL foo(textvariable) That seems like a lot of complexity for no real benefit, to me. no, you can to specify a expected result type - it's very for some convert or import functions. So we expect so out procedures will supports to OUT parameters, then implementation of this mechanism has minimal overhead to current implementation. Just to add types of OUT parameters to searching algorithm. More - it is just consistent with overloading idea. Why the OUT parameters should be removed from procedure parameters? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
Tom Lane wrote: Darren Duncan dar...@darrenduncan.net writes: Since Pg's FUNCTION already seems to take on both roles, so overloading the meaning of the FUNCTION keyword, like what a C function or a Perl sub does, where returning VOID means procedure, then what is being added by a distinct PROCEDURE? You might care to go back and re-read some of the extensive prior threads about this, but to my mind the main thing that would justify inventing a separate PROCEDURE facility is if procedures were to execute outside the transaction system, so that they could start and stop transactions for themselves. This is unlike a function which necessarily executes inside an already-running transaction. Of course a lot of questions would need to be answered about error-handling behavior and so forth, but that's a capability that a LOT of people have asked for. That is a very strong rationale in my mind to have clearly distinct kinds of routines, where one kind is implicitly entirely contained in a transaction and the other kind can cross transaction boundaries or control transactions. I support the separation on those grounds alone, though it also makes sense that the 2 kinds can have additional ways to distinguish them. -- Darren Duncan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
2010/9/9 Darren Duncan dar...@darrenduncan.net: Tom Lane wrote: Darren Duncan dar...@darrenduncan.net writes: Since Pg's FUNCTION already seems to take on both roles, so overloading the meaning of the FUNCTION keyword, like what a C function or a Perl sub does, where returning VOID means procedure, then what is being added by a distinct PROCEDURE? You might care to go back and re-read some of the extensive prior threads about this, but to my mind the main thing that would justify inventing a separate PROCEDURE facility is if procedures were to execute outside the transaction system, so that they could start and stop transactions for themselves. This is unlike a function which necessarily executes inside an already-running transaction. Of course a lot of questions would need to be answered about error-handling behavior and so forth, but that's a capability that a LOT of people have asked for. That is a very strong rationale in my mind to have clearly distinct kinds of routines, where one kind is implicitly entirely contained in a transaction and the other kind can cross transaction boundaries or control transactions. I support the separation on those grounds alone, though it also makes sense that the 2 kinds can have additional ways to distinguish them. -- Darren Duncan Functions should be under transaction always, but procedures when people like. There is BEGIN ATOMIC ... END block defined in SQL/PSM and procedure can be defined as ATOMIC or non ATOMIC. For me - most important difference is activation - function is activated from SELECT statement - and SELECT has plan - the result is hardly specified, procedure is activated by CALL statement - there are not plan - the result isn't limited. Regards Pavel Stehule -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
On Thu, Sep 9, 2010 at 4:29 PM, Pavel Stehule pavel.steh...@gmail.com wrote: so I can to write CREATE PROCEDURE foo(OUT a int) ... and CREATE PROCEDURE foo(OUT a varchar) ... and then when I use a statement CALL is correct procedure selected CALL foo(textvariable) That seems like a lot of complexity for no real benefit, to me. no, you can to specify a expected result type - it's very for some convert or import functions. So we expect so out procedures will supports to OUT parameters, then implementation of this mechanism has minimal overhead to current implementation. Just to add types of OUT parameters to searching algorithm. More - it is just consistent with overloading idea. Why the OUT parameters should be removed from procedure parameters? I think the question is whether there's something broken enough about the current system to warrant doing something different, and I guess my answer would be no. To be honest, I am already pretty unhappy with the changes that make it impossible to redefined foo(a int) as foo(anteater int), which is a perfectly reasonable thing to want to do but which is now forbidden because someone MIGHT have called the function as foo(a := 3), and I certainly don't want to make it any worse. Whether there are actually any stored queries that call the function this way (or at all) is doesn't matter: it's not allowed. So for a marginal notational convenience we have created dependency hell, where you must drop and recreate every dependent object to perform a trivial renaming. I think this is really quite horrible and would have argued against accepting this patch at the time if I'd realized what effect it was going to have. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
2010/9/9 Robert Haas robertmh...@gmail.com: On Thu, Sep 9, 2010 at 4:29 PM, Pavel Stehule pavel.steh...@gmail.com wrote: so I can to write CREATE PROCEDURE foo(OUT a int) ... and CREATE PROCEDURE foo(OUT a varchar) ... and then when I use a statement CALL is correct procedure selected CALL foo(textvariable) That seems like a lot of complexity for no real benefit, to me. no, you can to specify a expected result type - it's very for some convert or import functions. So we expect so out procedures will supports to OUT parameters, then implementation of this mechanism has minimal overhead to current implementation. Just to add types of OUT parameters to searching algorithm. More - it is just consistent with overloading idea. Why the OUT parameters should be removed from procedure parameters? I think the question is whether there's something broken enough about the current system to warrant doing something different, and I guess my answer would be no. To be honest, I am already pretty unhappy with the changes that make it impossible to redefined foo(a int) as foo(anteater int), which is a perfectly reasonable thing to want to do but which is now forbidden because someone MIGHT have called the function as foo(a := 3), and I certainly don't want to make it any worse. Whether there are actually any stored queries that call the function this way (or at all) is doesn't matter: it's not allowed. So for a marginal notational convenience we have created dependency hell, where you must drop and recreate every dependent object to perform a trivial renaming. I think this is really quite horrible and would have argued against accepting this patch at the time if I'd realized what effect it was going to have. yes, named parameters for functions created a new dependency. But this isn't possible for procedures. You can not to use a procedure inside view. So new dependency are not possible there. This important on procedures - it is little bit more outer from database. Pavel -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: to my mind the main thing that would justify inventing a separate PROCEDURE facility is if procedures were to execute outside the transaction system, so that they could start and stop transactions for themselves. That is the biggest distinction in my mind, too. Supporting multiple result sets just as if the queries were run as independent client-side statements would also be very important. I have seen implementations which support, for a single stored procedure, OUT parameters, a RETURN value, and multiple result sets -- all at the same time, as separate things. That seems rather overkill to me --- in particular, I don't understand the point of a RETURN value when there can be no caller to return a value to. Scalar OUT parameters could be sensible though; those could be returned to the client as a one-row result set. One point that has to be made is that returning multiple result sets as if they were successive queries restricts the client to reading the result sets serially; that is, you must read all of result A before you can look at result B, etc. While that's frequently good enough, an advantage of the return-some-cursors approach is that you can scan the cursors in parallel. I'm not sure whether we need to provide that flexibility in a procedure facility. One reason not to worry about it is that you can't return a cursor if the procedure is outside any transaction --- unless you make it a WITH HOLD cursor, which is mighty expensive and should certainly not be the default behavior. It might be sufficient to say that anyone needing that capability can return names of WITH HOLD cursors as scalar OUT parameters, or use the existing FUNCTION infrastructure. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
2010/9/9 Robert Haas robertmh...@gmail.com: On Thu, Sep 9, 2010 at 4:29 PM, Pavel Stehule pavel.steh...@gmail.com wrote: so I can to write CREATE PROCEDURE foo(OUT a int) ... and CREATE PROCEDURE foo(OUT a varchar) ... and then when I use a statement CALL is correct procedure selected CALL foo(textvariable) That seems like a lot of complexity for no real benefit, to me. no, you can to specify a expected result type - it's very for some convert or import functions. So we expect so out procedures will supports to OUT parameters, then implementation of this mechanism has minimal overhead to current implementation. Just to add types of OUT parameters to searching algorithm. More - it is just consistent with overloading idea. Why the OUT parameters should be removed from procedure parameters? I think the question is whether there's something broken enough about the current system to warrant doing something different, and I guess my answer would be no. To be honest, I am already pretty unhappy with the changes that make it impossible to redefined foo(a int) as foo(anteater int), which is a perfectly reasonable thing to want to do but which is now forbidden because someone MIGHT have called the function as foo(a := 3), and I certainly don't want to make it any worse. Whether there are actually any stored queries that call the function this way (or at all) is doesn't matter: it's not allowed. So for a marginal notational convenience we have created dependency hell, where you must drop and recreate every dependent object to perform a trivial renaming. I don't agree with you - this behave is because pg doesn't hold dependency between functions and preparsed SQL - so this is one the most simple protection. But if somebody appends a relations between views and functions to dictionary, then he can rechecks necessary views automatically. Regards Pavel Stehule I think this is really quite horrible and would have argued against accepting this patch at the time if I'd realized what effect it was going to have. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
Robert Haas robertmh...@gmail.com writes: To be honest, I am already pretty unhappy with the changes that make it impossible to redefined foo(a int) as foo(anteater int), which is a perfectly reasonable thing to want to do but which is now forbidden because someone MIGHT have called the function as foo(a := 3), and I certainly don't want to make it any worse. Whether there are actually any stored queries that call the function this way (or at all) is doesn't matter: it's not allowed. BTW, before anyone suggests it: it wouldn't really improve matters if we were to allow renaming so long as we couldn't find any such calls in stored queries. We don't have any ability to track calls occuring in stored procedures, let alone on the client side; so a rename would still put you at very substantial risk of breaking things. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
Tom Lane t...@sss.pgh.pa.us wrote: I don't understand the point of a RETURN value when there can be no caller to return a value to. The version of the draft standard I last saw allowed something like: SET x = CALL sp(param_a, param_b); I seem to remember Sybase supported a return value as well as OUT parameters, too; I think there it was limited to integer values and was conventionally used to indicate overall success or failure of the procedure. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
Kevin Grittner wrote: Tom Lane t...@sss.pgh.pa.us wrote: to my mind the main thing that would justify inventing a separate PROCEDURE facility is if procedures were to execute outside the transaction system, so that they could start and stop transactions for themselves. That is the biggest distinction in my mind, too. Supporting multiple result sets just as if the queries were run as independent client-side statements would also be very important. I have seen implementations which support, for a single stored procedure, OUT parameters, a RETURN value, and multiple result sets -- all at the same time, as separate things. I haven't reviewed stored procedures in the SQL standard since an early draft proposal years ago, so I don't know what the current state of that is, but if PostgreSQL approaches this, it'd be nice to implement as many of the above as are not in conflict with requirements of the standard. If it was reasonable I would go further in splitting and have at least 4 distinct kinds of routines, here listed in order of invocablility (each routine kind can invoke anything above it on the list but not anything below it): 1. Expression-invoked pure functions that only have IN parameters and can not directly see the database or have any side-effects and are always in a transaction. Most operators are of this kind. 2. Statement-invoked routines that are pure like #1 but also have OUT/INOUT parameters instead of resulting in a value like a function. The assignment operator is of this kind. 3. Routines that *can* see and update the database but are otherwise like #2, and are always in a transaction. The general case of a SELECT or DML or DDL are of this kind. 4. Routines that can cross transaction boundaries or control transactions but are otherwise like #2 or #3. Transaction control statements are of this kind. If I understand correctly, the existing Pg FUNCTION is essentially #3 and the proposed PROCEDURE is essentially #4. Maybe I just have to RTFM but I don't know if it is possible now to declare a Pg FUNCTION that it stays in the restrictions of #1 or #2. But if not, then I think it would be valuable to do so, for assisting reliability and performance. -- Darren Duncan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
Pavel Stehule pavel.steh...@gmail.com writes: 2010/9/9 Robert Haas robertmh...@gmail.com: That seems like a lot of complexity for no real benefit, to me. no, you can to specify a expected result type - it's very for some convert or import functions. So we expect so out procedures will supports to OUT parameters, then implementation of this mechanism has minimal overhead to current implementation. Just to add types of OUT parameters to searching algorithm. It's *not* trivial, not at all. You are ignoring all of the semantic implications. Should foo(IN x int, OUT y int) be considered different from, and thus allowed to exist at the same time as, foo(IN x int, OUT y float)? If so, how do you represent that in the catalogs? Possibly more to the point, any such decision means that it'll be impossible to call any stored procedure without fully specifying the types of output arguments as well as input arguments, else the system can't tell which procedure you meant to call. That doesn't sound like a notational improvement to me. I'm with Robert: this would be a huge extra complication for a remarkably small amount of benefit. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
Tom Lane t...@sss.pgh.pa.us wrote: I'm with Robert: this would be a huge extra complication for a remarkably small amount of benefit. This is probably heresy, but unless it's required by the standard or drop-dead simple to allow, I'd be fine with *not* supporting overloading of stored procedure names based on argument types at all. I can see the need for to support it for functions; I can't think where it would be all that useful for stored procedures. If unique stored procedure names were required, it seems we might be able to allow assignment casts on parameters, which might be more useful. I'm probably missing some good use case -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
2010/9/9 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: 2010/9/9 Robert Haas robertmh...@gmail.com: That seems like a lot of complexity for no real benefit, to me. no, you can to specify a expected result type - it's very for some convert or import functions. So we expect so out procedures will supports to OUT parameters, then implementation of this mechanism has minimal overhead to current implementation. Just to add types of OUT parameters to searching algorithm. It's *not* trivial, not at all. You are ignoring all of the semantic implications. Should foo(IN x int, OUT y int) be considered different from, and thus allowed to exist at the same time as, foo(IN x int, OUT y float)? If so, how do you represent that in the catalogs? Possibly more to the point, any such decision means that it'll be impossible to call any stored procedure without fully specifying the types of output arguments as well as input arguments, else the system can't tell which procedure you meant to call. That doesn't sound like a notational improvement to me. it can be a foo(int, int) and foo(int, float) in catalog. These lists are distinct so there are not a problem. I'm with Robert: this would be a huge extra complication for a remarkably small amount of benefit. I don't agree - you see a procedure like some void function, and it's not true Regards Pavel regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
2010/9/9 Kevin Grittner kevin.gritt...@wicourts.gov:
Tom Lane t...@sss.pgh.pa.us wrote:
I'm with Robert: this would be a huge extra complication for a
remarkably small amount of benefit.
This is probably heresy, but unless it's required by the standard or
drop-dead simple to allow, I'd be fine with *not* supporting
overloading of stored procedure names based on argument types at
all. I can see the need for to support it for functions; I can't
think where it would be all that useful for stored procedures. If
unique stored procedure names were required, it seems we might be
able to allow assignment casts on parameters, which might be more
useful.
I'm probably missing some good use case
for example - value transformation from / to bytea
CREATE FUNCTION send(int);
CREATE FUNCTION send(text);
CREATE FUNCTION recv(int);
CREATE FUNCTION recv(text)
then you can write
BEGIN
send('ahoj');
send(10);
recv(textvar);
recv(numvar);
Regards
Pavel Stehule
-Kevin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
Tom Lane wrote: One point that has to be made is that returning multiple result sets as if they were successive queries restricts the client to reading the result sets serially; that is, you must read all of result A before you can look at result B, etc. One aspect that I don't really like about SQL contrasted with typical other languages is in how query results are typically returned out of band like the above describes, rather than explicitly either via an OUT/INOUT parameter or as a function result relation value. -- Darren Duncan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
2010/9/9 Darren Duncan dar...@darrenduncan.net: Kevin Grittner wrote: Tom Lane t...@sss.pgh.pa.us wrote: to my mind the main thing that would justify inventing a separate PROCEDURE facility is if procedures were to execute outside the transaction system, so that they could start and stop transactions for themselves. That is the biggest distinction in my mind, too. Supporting multiple result sets just as if the queries were run as independent client-side statements would also be very important. I have seen implementations which support, for a single stored procedure, OUT parameters, a RETURN value, and multiple result sets -- all at the same time, as separate things. I haven't reviewed stored procedures in the SQL standard since an early draft proposal years ago, so I don't know what the current state of that is, but if PostgreSQL approaches this, it'd be nice to implement as many of the above as are not in conflict with requirements of the standard. If it was reasonable I would go further in splitting and have at least 4 distinct kinds of routines, here listed in order of invocablility (each routine kind can invoke anything above it on the list but not anything below it): 1. Expression-invoked pure functions that only have IN parameters and can not directly see the database or have any side-effects and are always in a transaction. Most operators are of this kind. 2. Statement-invoked routines that are pure like #1 but also have OUT/INOUT parameters instead of resulting in a value like a function. The assignment operator is of this kind. 3. Routines that *can* see and update the database but are otherwise like #2, and are always in a transaction. The general case of a SELECT or DML or DDL are of this kind. 4. Routines that can cross transaction boundaries or control transactions but are otherwise like #2 or #3. Transaction control statements are of this kind. If I understand correctly, the existing Pg FUNCTION is essentially #3 and the proposed PROCEDURE is essentially #4. Immutable functions are very near to #1. Actually PostgreSQL OUT parameters are implemented as returned one composite value. Regards Pavel Maybe I just have to RTFM but I don't know if it is possible now to declare a Pg FUNCTION that it stays in the restrictions of #1 or #2. But if not, then I think it would be valuable to do so, for assisting reliability and performance. -- Darren Duncan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
On tor, 2010-09-09 at 13:08 -0700, Darren Duncan wrote: Since Pg's FUNCTION already seems to take on both roles, so overloading the meaning of the FUNCTION keyword, like what a C function or a Perl sub does, where returning VOID means procedure, then what is being added by a distinct PROCEDURE? I'd just like to have the CALL statement, because CALL do_something(); looks better than SELECT do_something(); Small details ... -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
On tor, 2010-09-09 at 16:16 -0400, Tom Lane wrote: You might care to go back and re-read some of the extensive prior threads about this, but to my mind the main thing that would justify inventing a separate PROCEDURE facility is if procedures were to execute outside the transaction system, so that they could start and stop transactions for themselves. Given what the SQL standard says, a procedure certainly has to be defined as syntactic sugar for function returns void. Special transaction handling would then have to be an additional attribute of the procedure. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
Peter Eisentraut wrote: On tor, 2010-09-09 at 13:08 -0700, Darren Duncan wrote: Since Pg's FUNCTION already seems to take on both roles, so overloading the meaning of the FUNCTION keyword, like what a C function or a Perl sub does, where returning VOID means procedure, then what is being added by a distinct PROCEDURE? I'd just like to have the CALL statement, because CALL do_something(); looks better than SELECT do_something(); Small details ... I don't have a problem with the PERFORM keyword aside from it being more verbose than CALL, except that, with 8.4 anyway, PERFORM doesn't seem to work client-side. It seems that to invoke a VOID function foo client-side I still have to say SELECT foo(...); because saying PERFORM foo(...); is unfortunately a syntax error. -- Darren Duncan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
On Sun, Sep 5, 2010 at 2:26 PM, Peter Eisentraut pete...@gmx.net wrote: On fre, 2010-09-03 at 16:18 -0400, Tom Lane wrote: Part of the reason it's sat on TODO is lack of consensus about how such a feature ought to look/work; particularly since most of the discussion about it has considered that it'd go along with stored procedures executing outside of transactions. I would probably be a mistake to tie these features together. They are tricky enough separately. Hm, do you think it would be possible to request manual transaction state when setting up the procedure (or reserve that ability for the future)? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
On 04/09/10 17:16, Merlin Moncure wrote: Curious: is mulitset handling as you see it supported by the current v3 protocol? The manual says: The response to a SELECT query (or other queries that return row sets, such as EXPLAIN or SHOW) normally consists of RowDescription, zero or more DataRow messages, and then CommandComplete. COPY to or from the frontend invokes special protocol as described in Section 46.2.5. All other query types normally produce only a CommandComplete message. Since a query string could contain several queries (separated by semicolons), there might be several such response sequences before the backend finishes processing the query string. ReadyForQuery is issued when the entire string has been processed and the backend is ready to accept a new query string. If a multiple return sets from a procedure are returned just like multiple return sets from multiple queries, that's already covered by the protocol. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 04/09/10 17:16, Merlin Moncure wrote: Curious: is mulitset handling as you see it supported by the current v3 protocol? If a multiple return sets from a procedure are returned just like multiple return sets from multiple queries, that's already covered by the protocol. Well, the protocol says you can do it, but it would likely require significant work to make clients deal with it sanely. Also, the part of the protocol document Heikki is quoting is for the legacy simple query mode. We deliberately designed this behavior *out* of the extended query mode. So for example you couldn't use out-of-line parameters with such a feature, unless there's a protocol redesign. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
On fre, 2010-09-03 at 16:18 -0400, Tom Lane wrote: Part of the reason it's sat on TODO is lack of consensus about how such a feature ought to look/work; particularly since most of the discussion about it has considered that it'd go along with stored procedures executing outside of transactions. I would probably be a mistake to tie these features together. They are tricky enough separately. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
On 9/5/2010 2:05 PM, Heikki Linnakangas wrote: On 04/09/10 17:16, Merlin Moncure wrote: Curious: is mulitset handling as you see it supported by the current v3 protocol? The manual says: The response to a SELECT query (or other queries that return row sets, such as EXPLAIN or SHOW) normally consists of RowDescription, zero or more DataRow messages, and then CommandComplete. COPY to or from the frontend invokes special protocol as described in Section 46.2.5. All other query types normally produce only a CommandComplete message. Since a query string could contain several queries (separated by semicolons), there might be several such response sequences before the backend finishes processing the query string. ReadyForQuery is issued when the entire string has been processed and the backend is ready to accept a new query string. If a multiple return sets from a procedure are returned just like multiple return sets from multiple queries, that's already covered by the protocol. Just as a side note, libpqtypes can emulate this using composite arrays; a feature we abuse internally. It is actually the primary justification we had for developing that portion of libpqtypes; initially we stayed clear of arrays and composites. create table fork_t (fork_id, rev_id, size, block_ids int8[], ...) create table rev_t (rev_id, blah, blah, fork_t[]); /* this is my favorite part of libpqtypes */ PGarray arr; PQgetf(result, tup_num, %rev_t[], field_num, arr); Now loop the array arr and getf(arr.res) for each rev_t, which allows you to getf each fork_t in the fork_t[], etc I *know* it is not pure multiset'n, but it sure gets the job done (in a completely different way, I know). However, I'm sure those reading this list can see the possiblities ;) Andrew Chernow eSilo, LLC. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
On Fri, Sep 3, 2010 at 4:07 PM, Josh Berkus j...@agliodbs.com wrote: I noticed in postgres you cannot return multiple result sets from a stored procedure (surprisingly as it looks like a very good dbms). That feature has been on the TODO list for years. However, nobody has stepped forward to either write it, or to fund working on it. If your company has programmers or money to build this feature, it could probably get done fairly quickly (as in, next version). Also as mentioned upthread there are effective workarounds if you poke around a bit. This is a FAQ, and there are about 3-4 solid methods (if you search the archives) that cover most problems you would be looking at multiple results sets to solve. I suppose this is why there hasn't been more of an effort to do this earlier. People asking for this are typically dispossessed SQL server developers who haven't quite gotten used to the postgres way of things. Not that proper stored procedures wouldn't be great -- they would be -- but they are not the only way to solve these types of problems. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
2010/9/4 Merlin Moncure mmonc...@gmail.com: On Fri, Sep 3, 2010 at 4:07 PM, Josh Berkus j...@agliodbs.com wrote: I noticed in postgres you cannot return multiple result sets from a stored procedure (surprisingly as it looks like a very good dbms). That feature has been on the TODO list for years. However, nobody has stepped forward to either write it, or to fund working on it. If your company has programmers or money to build this feature, it could probably get done fairly quickly (as in, next version). Also as mentioned upthread there are effective workarounds if you poke around a bit. This is a FAQ, and there are about 3-4 solid methods (if you search the archives) that cover most problems you would be looking at multiple results sets to solve. I suppose this is why there hasn't been more of an effort to do this earlier. People asking for this are typically dispossessed SQL server developers who haven't quite gotten used to the postgres way of things. Not that proper stored procedures wouldn't be great -- they would be -- but they are not the only way to solve these types of problems. I had a prototype that can do multirecordset. But implementation of non transact procedures needs a hundreds hours of work: * outer SPI * parametrization for non planner statements - for CALL statement * explicit transaction control for procedures. * client API support for multirecordset * better support for OUT variables. Regards Pavel Stehule merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
On Sat, Sep 4, 2010 at 10:10 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Also as mentioned upthread there are effective workarounds if you poke around a bit. This is a FAQ, and there are about 3-4 solid methods (if you search the archives) that cover most problems you would be looking at multiple results sets to solve. I suppose this is why there hasn't been more of an effort to do this earlier. People asking for this are typically dispossessed SQL server developers who haven't quite gotten used to the postgres way of things. Not that proper stored procedures wouldn't be great -- they would be -- but they are not the only way to solve these types of problems. I had a prototype that can do multirecordset. But implementation of non transact procedures needs a hundreds hours of work: * outer SPI * parametrization for non planner statements - for CALL statement * explicit transaction control for procedures. * client API support for multirecordset * better support for OUT variables. Curious: is mulitset handling as you see it supported by the current v3 protocol? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
2010/9/4 Merlin Moncure mmonc...@gmail.com: On Sat, Sep 4, 2010 at 10:10 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Also as mentioned upthread there are effective workarounds if you poke around a bit. This is a FAQ, and there are about 3-4 solid methods (if you search the archives) that cover most problems you would be looking at multiple results sets to solve. I suppose this is why there hasn't been more of an effort to do this earlier. People asking for this are typically dispossessed SQL server developers who haven't quite gotten used to the postgres way of things. Not that proper stored procedures wouldn't be great -- they would be -- but they are not the only way to solve these types of problems. I had a prototype that can do multirecordset. But implementation of non transact procedures needs a hundreds hours of work: * outer SPI * parametrization for non planner statements - for CALL statement * explicit transaction control for procedures. * client API support for multirecordset * better support for OUT variables. Curious: is mulitset handling as you see it supported by the current v3 protocol? if you see multirecordset as setof cursors, then you don't need changes. But in my implementation, I did a few changes, if I remember well, because my implementation wasn't based on setof cursors. Pavel merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
On Thu, Sep 2, 2010 at 7:17 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from John Adams's message of jue sep 02 18:25:45 -0400 2010: I noticed in postgres you cannot return multiple result sets from a stored procedure (surprisingly as it looks like a very good dbms). If you're really intent about doing this, you can emulate it by returning a set of refcursors. Also arrays can work pretty well, depending on how much data there is and where it's going. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
I noticed in postgres you cannot return multiple result sets from a stored procedure (surprisingly as it looks like a very good dbms). That feature has been on the TODO list for years. However, nobody has stepped forward to either write it, or to fund working on it. If your company has programmers or money to build this feature, it could probably get done fairly quickly (as in, next version). -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
Josh Berkus j...@agliodbs.com writes: I noticed in postgres you cannot return multiple result sets from a stored procedure (surprisingly as it looks like a very good dbms). That feature has been on the TODO list for years. However, nobody has stepped forward to either write it, or to fund working on it. If your company has programmers or money to build this feature, it could probably get done fairly quickly (as in, next version). Part of the reason it's sat on TODO is lack of consensus about how such a feature ought to look/work; particularly since most of the discussion about it has considered that it'd go along with stored procedures executing outside of transactions. It's not just a matter of needing to find some programming manpower. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
Excerpts from John Adams's message of jue sep 02 18:25:45 -0400 2010: I noticed in postgres you cannot return multiple result sets from a stored procedure (surprisingly as it looks like a very good dbms). If you're really intent about doing this, you can emulate it by returning a set of refcursors. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
