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
Attachment:
signature.asc
Description: OpenPGP digital signature
