Обсуждение: Full Stored Procedure Support, any time soon ?
Hi,
My firm is considering a very large move from PAID RDBMS to Postgresql.
The lack of full support for Stored Procedures is currently a deal breaker.
We use many features, like:
- output parameters
- Callable statements to call the procs
- Multiple result sets
- nested procedure calls
Is there in the *near* future the possibility of adding support for these?
Simple functions are _not_ enough. EnterpriseDB seems to have such support but it is a payed for feature.
Any help will be greatly appreciated.
Many thanks
Noeldr
On 11/30/2013 12:17 PM, Noel Diaz wrote:
My firm is considering a very large move from PAID RDBMS to Postgresql.The lack of full support for Stored Procedures is currently a deal breaker.We use many features, like:- output parameters- Callable statements to call the procs- Multiple result sets- nested procedure callsIs there in the *near* future the possibility of adding support for these?
we're doing much the same, moving from that big O to Postgres, but we opted to STOP using stored procedures and instead reimplement our core business logic as a middleware service (which happens to be in Java but really could be in most any language that has decent database and messaging and/or RPC support). we find its a lot easier to debug the middleware than complex problems buried deep in stored procedures. We still use SOME postgres functions, for performance optimization, triggers (for partitioning) and so forth, but we don't have the bulk of our business logic there. the client applications are not allowed ANY database access, they have to go through the middleware.
-- john r pierce 37N 122W somewhere on the middle of the left coast
On 30 Nov 2013, at 21:17, Noel Diaz <zerg2k@yahoo.com> wrote: > Hi, Welcome to the list! > My firm is considering a very large move from PAID RDBMS to Postgresql. > The lack of full support for Stored Procedures is currently a deal breaker. We’re not intimately familiar with your paid RDBMS, so perhaps you could elaborate on what _exactly_ you’re missing? I’mgoing to wager a guess to what you mean though, hopefully that helps clear things up. As far as people here are concerned, procedures are merely functions that don’t return anything. We have those. > We use many features, like: > - output parameters Functions have IN, OUT, INOUT and VARIADIC parameters. They also have return parameters. How does that not cover what yourequire? http://www.postgresql.org/docs/9.2/static/sql-createfunction.html > - Callable statements to call the procs Not sure what you mean here. Obviously functions can be called, otherwise what would be their point? You can use either select my_func() for functions that return a single value or select … from my_func() for functions thatreturn result sets. > - Multiple result sets Since you’re talking about procedures, you can’t possibly mean that those return multiple result sets? Functions can handle multiple result sets internally. You can loop through as many result sets in your function as you like. If the functions return a refcursor or a set of records, they can even _return_ multiple result sets, but you’ll have tocast those function result rows to a meaningful type before being able to use them. > - nested procedure calls Function calls can be nested, they even support recursion. > Simple functions are _not_ enough. Why not? What don’t they do that you require? Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
Alban,
Thank you very much for the Link.
Let me see if I can explain from the beginning:
I see that output parameters are possible in functions as well as return values!! So that is a plus!
When I mentioned "Callable Statements" I was referring to the way we call the procs from java : http://jdbc.postgresql.org/documentation/91/callproc.html
I was not sure that "cstm.prepareCall" was allowed with functions (all examples I saw used preparedStatements) but after further digging further it seems possible as the link above indicates. This is another plus!
When I mentioned "multiple result sets", That is EXACTLY what it means
You can create a procedure that does something like this:
SELECT * FROM customers WHERE ... ; /* any complex query */
SELECT * FROM orders ............; /* any other set that DOES NOT needs to be joined to the above*/
.... And so on /* Sometimes this is useful because intermediate results do not need to be sent back to the clients etc */
With that we can, in ONE round trip, process data and we use the cstmt.getMoreResults / cstmt.getResultSet Pattern like so: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.apdv.java.doc%2Fdoc%2Ftjvjdmlt.htm
Hopefully I shed some light in my incomplete description
Thank you for the prompt reply,
Noeldr
On Saturday, November 30, 2013 4:33 PM, Alban Hertroys <haramrae@gmail.com> wrote:
On 30 Nov 2013, at 21:17, Noel Diaz <zerg2k@yahoo.com> wrote:
> Hi,
Welcome to the list!
> My firm is considering a very large move from PAID RDBMS to Postgresql.
> The lack of full support for Stored Procedures is currently a deal breaker.
We’re not intimately familiar with your paid RDBMS, so perhaps you could elaborate on what _exactly_ you’re missing? I’m going to wager a guess to what you mean though, hopefully that helps clear things up.
As far as people here are concerned, procedures are merely functions that don’t return anything. We have those.
> We use many features, like:
> - output parameters
Functions have IN, OUT, INOUT and VARIADIC parameters. They also have return parameters. How does that not cover what you require?
http://www.postgresql.org/docs/9.2/static/sql-createfunction.html
> - Callable statements to call the procs
Not sure what you mean here. Obviously functions can be called, otherwise what would be their point?
You can use either select my_func() for functions that return a single value or select … from my_func() for functions that return result sets.
> - Multiple result sets
Since you’re talking about procedures, you can’t possibly mean that those return multiple result sets?
Functions can handle multiple result sets internally. You can loop through as many result sets in your function as you like.
If the functions return a refcursor or a set of records, they can even _return_ multiple result sets, but you’ll have to cast those function result rows to a meaningful type before being able to use them.
> - nested procedure calls
Function calls can be nested, they even support recursion.
> Simple functions are _not_ enough.
Why not? What don’t they do that you require?
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
> Hi,
Welcome to the list!
> My firm is considering a very large move from PAID RDBMS to Postgresql.
> The lack of full support for Stored Procedures is currently a deal breaker.
We’re not intimately familiar with your paid RDBMS, so perhaps you could elaborate on what _exactly_ you’re missing? I’m going to wager a guess to what you mean though, hopefully that helps clear things up.
As far as people here are concerned, procedures are merely functions that don’t return anything. We have those.
> We use many features, like:
> - output parameters
Functions have IN, OUT, INOUT and VARIADIC parameters. They also have return parameters. How does that not cover what you require?
http://www.postgresql.org/docs/9.2/static/sql-createfunction.html
> - Callable statements to call the procs
Not sure what you mean here. Obviously functions can be called, otherwise what would be their point?
You can use either select my_func() for functions that return a single value or select … from my_func() for functions that return result sets.
> - Multiple result sets
Since you’re talking about procedures, you can’t possibly mean that those return multiple result sets?
Functions can handle multiple result sets internally. You can loop through as many result sets in your function as you like.
If the functions return a refcursor or a set of records, they can even _return_ multiple result sets, but you’ll have to cast those function result rows to a meaningful type before being able to use them.
> - nested procedure calls
Function calls can be nested, they even support recursion.
> Simple functions are _not_ enough.
Why not? What don’t they do that you require?
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
Alban Hertroys wrote on 30.11.2013 22:34: >> - Multiple result sets > > Since you’re talking about procedures, you can’t possibly mean that those return multiple result sets? Yes, basically something like this: create procedure foobar() begin select * from table_1; select * from table_2 end; I know that at least SQL Server and MySQL can do that. Maybe others as well (Sybase most probably). But I always failed do see the actual advantage of that because the results can't be "used" any further (e.g. in a JOIN ora subselect). I also don't understand why having a single procedure doing a lot of stuff is better than having several procedures (or functions)doing one defined thing. From a JDBC point of view the code simply asks the Statement whether it has any more result sets and loops over this until all results are returned. Thomas
Thomas, RIGHT ON!
Sometimes I have seen this feature used to hide multiple round trips and intermediate result processing from the clients
Some others is simply because the client is generating a predefined data structure that has little resemblance to the schema and this "feature" becomes convenient
Some others is "ad-hoc" tools that allow to see multiple datasets in the UI, etc...
Anyway I just saw this in the TODO list: (http://wiki.postgresql.org/wiki/Todo) :
- _ 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
- Gathering specs and discussion on feature (post 9.1)
Does anybody know if any the committers are working on that list?On Saturday, November 30, 2013 5:21 PM, Thomas Kellerer <spam_eater@gmx.net> wrote:Alban Hertroys wrote on 30.11.2013 22:34:
>> - Multiple result sets
>
> Since you’re talking about procedures, you can’t possibly mean that those return multiple result sets?
Yes, basically something like this:
create procedure foobar()
begin
select * from table_1;
select * from table_2
end;
I know that at least SQL Server and MySQL can do that. Maybe others as well (Sybase most probably).
But I always failed do see the actual advantage of that because the results can't be "used" any further (e.g. in a JOIN or a subselect).
I also don't understand why having a single procedure doing a lot of stuff is better than having several procedures (or functions) doing one defined thing.
From a JDBC point of view the code simply asks the Statement whether it has any more result sets
and loops over this until all results are returned.
Thomas
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 11/30/2013 2:30 PM, Noel Diaz wrote:
- 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
- Gathering specs and discussion on feature (post 9.1)
Does anybody know if any the committers are working on that list?
I don't think the libpq API will allow that first one, a SQL query can only return a single recordset of rows that all have the same fields.
-- john r pierce 37N 122W somewhere on the middle of the left coast
John R Pierce wrote on 30.11.2013 23:47: > On 11/30/2013 2:30 PM, Noel Diaz wrote: >> This might involve the control of transaction state and the return of multiple result sets >> >> * PL/pgSQL stored procedure returning multiple result sets (SELECTs)? <http://archives.postgresql.org/pgsql-general/2008-10/msg00454.php> >> * Proposal: real procedures again (8.4) <http://archives.postgresql.org/pgsql-hackers/2007-10/msg01375.php> >> * http://archives.postgresql.org/pgsql-hackers/2010-09/msg00542.php >> * Gathering specs and discussion on feature (post 9.1) <http://archives.postgresql.org/pgsql-hackers/2011-04/msg01149.php> >> >> Does anybody know if any the committers are working on that list? > > I don't think the libpq API will allow that first one, a SQL query can only return a single recordset >of rows that all have the same fields. I don't think that's a limitation. The client will still iterate through one result set at a time. The "potential" result sets would need to be somehow "prepared to be sent" on the server. When the client has exhausted oneresult set, it requests the next one (or asks if there _is_ a next one). Only then the server starts streaming the results.That way libpg would only deal with a single result at a time. I don't think SQL Server or MySQL actually send all results at once (definitely not when using JDBC)
On 11/30/2013 3:27 PM, Thomas Kellerer wrote: > I don't think that's a limitation. The client will still iterate > through one result set at a time. PQexec etc return a PGresult * the various PQxxxxx functions that accept PGresult * as an argument, each only return info about the single resultset, like PQntuples, PQnfields, PQgetvalue, PQfname, PQfnumber, etc... I guess we'd need some new functions to return the number of resultsets, and choose a resultset by index number, then those above functions would refer to the 'currently chosen' PQresult ? or at least a res = PQnextresult(PGresult *res), which returns 0 if there is no next resultset ? -- john r pierce 37N 122W somewhere on the middle of the left coast
Noel Diaz wrote > T_ 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 > * Gathering specs and discussion on feature (post 9.1) > > Does anybody know if any the committers are working on that list? I have not seen any recent discussions on the list pertaining to implementing callable stored procedures. Thus nothing like this has a chance to make it out with the next release 6+ months from now. The earliest possible implementation would be 1.5 years from now and that is only if someone decides to dedicate the next year solely on this feature. Some "foundation" work has been ongoing recently, notably "background workers", which is how these things are likely to play out. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Full-Stored-Procedure-Support-any-time-soon-tp5780999p5781040.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.