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@xxxxxxxxxx> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
