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

Below is a very good summary of the limitations of our function
capabilities compared to procedures, e.g.:

o no transaction control in functions
o no multi-query return values without using special syntax

I don't think we can cleanly enable the second capability, but could we
allow transaction control for functions that are not called inside a
multi-statement transaction?

FYI, right now when you call a function all statements are assumed to be
in a single transaction, and allowing transaction control inside a
function would mean that each statement in a function is its own
transaction _unless_ transaction control is specified. There would
certainly need to be special syntax to enable this.

Is there a TODO here?

---------------------------------------------------------------------------

Vladimir Dzhuvinov wrote:
-- Start of PGP signed section.
> 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
>
-- End of PGP section, PGP failed!

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +