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

Hi Merlin,

>> 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.

> 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.

I was speaking about how it's done in MySQL. And I liked their approach
of clear separation of responsibility between functions and stored
procedures. At first I didn't quite understand their point, but then,
during the development of my app, I gradually began to appreciate it.

To sum up how it's done in MySQL:

Functions are meant for tasks such as string operations, date/calendar
functions, maths, encryption. They are allowed to operate only on their
arguments. And they are stackable, just as functions in other languages
like C.

Stored procedures are meant to be programs that work on the data.
Hence they allowed to access tables, they can start explicit
transactions and they can execute plain arbitrary SELECTs that pass
their rows straight to the client. And stored procedures are "nestable"
- akin to include() in PHP.

I suspect that the present situation with Postgres reflects the way the
software developed over the years. Perhaps in the very beginning the
Postgres developers introduced functions which more or less resembled
the "plain" functions of MySQL today. But then users might have pressed
for a method to store their table manipulation logic on the server, and
then for some reason it had been decided to overload functions with this
extra responsibility, rather than create a separate clean "stored
procedure" class.

So today Postgres has got functions which are very feature-full
(compared with functions in MySQL), but still fall short of what
traditional stored procedures can provide.

Yes, I was very much pleased with a number of Postgres features, such as
the ability to do a tighter data definition using checks and
constraints. Postgres allows for a much richer data model when I compare
it with MySQL. I decided to put Postgres aside simply because it doesn't
allow the definition of *clean* stored procedures (as I'm used to them
in MySQL). And I didn't like the idea of twisting the PG function model
around to accommodate my existing MySQL stored procedure logic. I abhor
doing ugly things with code :)

Pavel stated interest to work on the addition of stored procedures to
Postgres provided he finds sponsorship. Right now I don't see much
benefit investing money into such a venture, besides I've got my hands
full with the day-to-day management of my own project. So far MySQL has
been doing its job well and for the near future it looks like I'm
staying on it.

> 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

Well, MySQL does allow for exception handling within SPs, although there
are some shortcomings (if you define a generic handler you cannot obtain
precise info on the error type).

> 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.

Oh, I am not missing the big picture: Quit programming and take up the
job of a lazy millionaire :)

> ok :-) enough advocacy...
> merlin

Cheers,

Vladimir
--
Vladimir Dzhuvinov * www.valan.net * PGP key ID AC9A5C6C