Re: PL/pgSQL stored procedure returning multiple result sets (SELECTs)?

On Tue, Oct 14, 2008 at 3:45 PM, Vladimir Dzhuvinov <vd(at)valan(dot)net> wrote:
>
> I want to tell you why I find stored procedures useful and summarise my
> understanding on how they differ from functions. I hope this user
> perspective would be helpful to a future Postgres implementation.
>
>
> So what is my use of stored procedures?
>
> I work on a system for internal payments between the employees of a
> company. The design called for a clear separation of clients and server,
> where responsibilities should be clear cut and client software should
> know as little as possible about the data model on the SQL server and
> its relational implementation. It's like going out with a beautiful
> woman - you just want to enjoy her fair qualities and don't really want
> to know how she's constructed ;)

This is a good philosophy, and also it makes me think you will be more
comfortable over here. PostgreSQL is more popular with the 'in the
database' crowd. mysql caters more to the 'as little as possible in
the database' crowd.

> A function is... hmm, a function, a mapping: given a set of arguments it
> returns a single and well defined value: f(x,y) -> z
>
> The purpose of stored procedures, on the other hand, is to encapsulate
> an (arbitrary) bunch of SQL commands, a mini-program of sort.
>
> The other differences they have seem to be secondary, stemming from
> their purposes.

I think your understanding is off here. Functions can encapsulate
arbitrary collection of statements...as I said previously, there are
two principle differences:
*) functions have implicit created transaction, procedures do not
*) how you pass data to/from the procedure body. (functions return a
scalar, record, or a set)

Functions are limited in the sense that it is awkward to return
multiple sets, but are much more flexible how they can be integrated
into queries -- you can call a function anywhere a scalar or a set is
allowed -- in addition to the monolithic procedure style.

> 2. (leads from 1) Functions are stackable, stored procedures are "nestable":
>
> ADDTIME(NOW(), SEC_TO_TIME(3600));
>
> vs.
>
> CREATE PROCEDURE my_task()
> BEGIN
> ...
> CALL some_other_task(param1, @param2);
> ...
> END

you can do this easily via functions.

> 3. (also leads from 1) Functions must have a defined return type, stored
> procedures normally have no such requirement.

not so, functions can return void.

> 4. Functions have restriction on table access, they are only allowed to
> work on their IN arguments (MySQL). Stored procedures have virtually no
> limitations - they can execute arbitrary SQL - access tables, do
> transactions and pass data directly to the client using SELECTs.

This is completely incorrect. postgresql functions can do anything,
you are describing an 'immutable function' in postgresql parlance.
These are used in special cases like indexable expressions.

> I personally find the ability to do a direct SELECT from a stored
> procedure to the client extremely useful (MySQL 5+). It makes data
/> retrieval easier to program than having a stored procedure return open
> cursors or OUT parameters (saving additional SELECT queries after the
> CALL() ).

you can do this in postgreql, just only return 1 set...

create function get_foo() returns setof foo as
$$
select * from foo;
$$ language sql;

While the inability to return directly two sets from the same function
is annoying (I would use arrays today, this was one of the reasons why
we wrote libpqtypes), you have to understand that in virtually all
other respects postgresql pl/pgsql is light years beyond the lousy psm
implementation in mysql. The way we handle cursors, iteration, error
handing, optimizable expressions and such has undergone years of
refinement.

Just as a 'for example', look how you can trap errors and do some
recovery inside a pl/pgsql routine:
http://www.postgresql.org/docs/8.3/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

That feature alone can help you enormously. Lest you think I'm
biased, I dba a mysql box professionally...every time I pop into the
mysql shell I feel like I'm stepping backwards in time about 5 years.
Don't let the inability to return multiple sets trip you up...you are
missing the big picture.

ok :-) enough advocacy...
merlin