Re: Full Stored Procedure Support, any time soon ?

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.