Re: Full Stored Procedure Support, any time soon ?

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(at)gmail(dot)com> wrote:

On 30 Nov 2013, at 21:17, Noel Diaz <zerg2k(at)yahoo(dot)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.