Обсуждение: PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
Hi, I've got a financial MySQL database where the application accesses data through a layer of stored procedures. For various reasons I'm currently investigating my options to migrate to another SQL RDBMS. Postgresql seems to offer a few nice advantages over MySQL (e.g. stricter data integrity through checks and constraints, etc.) and I got quite excited about it. However, after consulting the docs and running a few tests, it looks like Postgresql misses a crucial feature which my application depends upon - returning multiple SELECT result sets from functions/stored procedures. To illustrate, I've got a number of MySQL stored procedures that look approximately like this: CREATE PROCEDURE list_user_accounts(IN user_id INT) BEGIN -- Return first result set (single row) SELECT * FROM users WHERE id = user_id; -- Return second result set (zero or more rows) SELECT * FROM accounts WHERE account_holder = user_id; END; So, is it true that as of Postgresql 8.3 there is no way to have a pgpqsql function return multiple SELECTs? Vladimir Dzhuvinov -- Vladimir Dzhuvinov * www.valan.net * PGP key ID AC9A5C6C
Вложения
am Mon, dem 13.10.2008, um 12:17:21 +0300 mailte Vladimir Dzhuvinov folgendes: > > However, after consulting the docs and running a few tests, it looks > like Postgresql misses a crucial feature which my application depends > upon - returning multiple SELECT result sets from functions/stored > procedures. > > So, is it true that as of Postgresql 8.3 there is no way to have a > pgpqsql function return multiple SELECTs? You can write so called SRF (Set Returning Function), read more about this here: http://www.postgresql.org/docs/current/static/xfunc-sql.html#XFUNC-SQL-TABLE-FUNCTIONS Simple example: test=# create or replace function srf (OUT a int, OUT b int) returns setof record as $$begin a:=1;b:=1;return next;a:=2;b:=3;return next; end;$$language plpgsql; CREATE FUNCTION test=*# select * from srf(); a | b ---+--- 1 | 1 2 | 3 (2 rows) or, simpler in plain sql: test=# create or replace function srf (OUT a int, OUT b int) returns setof record as $$select 1,2;select 1,3;$$language sql; CREATE FUNCTION test=*# test=*# test=*# select * from srf(); a | b ---+--- 1 | 3 (1 row) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
2008/10/13 Vladimir Dzhuvinov <vd@valan.net>: > Hi, > > I've got a financial MySQL database where the application accesses data > through a layer of stored procedures. For various reasons I'm currently > investigating my options to migrate to another SQL RDBMS. > > Postgresql seems to offer a few nice advantages over MySQL (e.g. > stricter data integrity through checks and constraints, etc.) and I got > quite excited about it. > > However, after consulting the docs and running a few tests, it looks > like Postgresql misses a crucial feature which my application depends > upon - returning multiple SELECT result sets from functions/stored > procedures. > > To illustrate, I've got a number of MySQL stored procedures that look > approximately like this: > > CREATE PROCEDURE list_user_accounts(IN user_id INT) > > BEGIN > > -- Return first result set (single row) > SELECT * FROM users WHERE id = user_id; > > -- Return second result set (zero or more rows) > SELECT * FROM accounts WHERE account_holder = user_id; > > END; > > > So, is it true that as of Postgresql 8.3 there is no way to have a > pgpqsql function return multiple SELECTs? Hello, it's true. You can use setof cursors instead. http://www.postgresql.org/docs/8.1/static/plpgsql-cursors.html regards Pavel Stehule > > > Vladimir Dzhuvinov > > -- > Vladimir Dzhuvinov * www.valan.net * PGP key ID AC9A5C6C > >
>> So, is it true that as of Postgresql 8.3 there is no way to have a >> pgpqsql function return multiple SELECTs? > it's true. Thank you for the definite answer, Pavel :) I came across a blog post of yours ( http://okbob.blogspot.com/2007/11/stacked-recordset-multirecordset.html ) as well as several list posts indicating that multiple result sets might be in the working. Should I check the situation again when 8.4 is released? > You can use setof cursors instead. Cursors, unfortunately, look cumbersome in this situation and will break the existing API (all transactions encapsulated within SPs, clients allowed to do CALL only). Anyway, thanks everyone for the cursors tip :) Vladimir -- Vladimir Dzhuvinov * www.valan.net * PGP key ID AC9A5C6C
Вложения
am Mon, dem 13.10.2008, um 11:34:03 +0200 mailte A. Kretschmer folgendes: > or, simpler in plain sql: > > test=# create or replace function srf (OUT a int, OUT b int) returns setof record as $$select 1,2;select 1,3;$$languagesql; > CREATE FUNCTION > test=*# > test=*# > test=*# select * from srf(); > a | b > ---+--- > 1 | 3 > (1 row) Sorry, i have overlooked that this isn't the expected result and thanks to Pavel for the rectification. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
2008/10/13 Vladimir Dzhuvinov <vd@valan.net>: > >>> So, is it true that as of Postgresql 8.3 there is no way to have a >>> pgpqsql function return multiple SELECTs? > >> it's true. > > Thank you for the definite answer, Pavel :) > > I came across a blog post of yours ( > http://okbob.blogspot.com/2007/11/stacked-recordset-multirecordset.html > ) as well as several list posts indicating that multiple result sets > might be in the working. Should I check the situation again when 8.4 is > released? > I have only very raw prototype, so I am sure, so this feature will not be in 8.4, and I am not sure about 8.5. It's nice feature, but I am not force to complete and clean code, and I am not able create patch. If you would do it, I am, with pleasure, send you source code, that allows multirecord sets. > >> You can use setof cursors instead. > > Cursors, unfortunately, look cumbersome in this situation and will break > the existing API (all transactions encapsulated within SPs, clients > allowed to do CALL only). Anyway, thanks everyone for the cursors tip :) > > > Vladimir > > -- > Vladimir Dzhuvinov * www.valan.net * PGP key ID AC9A5C6C > >
multi recordset and data type check was: Re: PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
От
Ivan Sergio Borgonovo
Дата:
On Mon, 13 Oct 2008 12:17:21 +0300 Vladimir Dzhuvinov <vd@valan.net> wrote: > CREATE PROCEDURE list_user_accounts(IN user_id INT) > > BEGIN > > -- Return first result set (single row) > SELECT * FROM users WHERE id = user_id; > > -- Return second result set (zero or more rows) > SELECT * FROM accounts WHERE account_holder = user_id; > > END; I'd say returning multiple recordset is useful to save connections and transferred data. You can't get the same with a left join (users fields will be repeated over and over) and you can't get the same with 2 separated statements since they will need 2 connections. But from the client side, suppose it PHP... if the first statement return no record and the second one return 3 records, how can I know? What about functions like pg_num_fields? -- Ivan Sergio Borgonovo http://www.webthatworks.it
>> I came across a blog post of yours ( >> http://okbob.blogspot.com/2007/11/stacked-recordset-multirecordset.html >> ) as well as several list posts indicating that multiple result sets >> might be in the working. Should I check the situation again when 8.4 is >> released? > I have only very raw prototype, so I am sure, so this feature will not > be in 8.4, and I am not sure about 8.5. It's nice feature, but I am > not force to complete and clean code, and I am not able create patch. > If you would do it, I am, with pleasure, send you source code, that > allows multirecord sets. Yes, I'll be glad to examine your patch. At least to get an idea of what's involved in implementing multiple result sets. Please, send the code or a link to it directly to my email (so as not to spam the list ;) Greetings from Bulgaria, Vladimir -- Vladimir Dzhuvinov * www.valan.net * PGP key ID AC9A5C6C
Вложения
On Mon, Oct 13, 2008 at 8:09 AM, Vladimir Dzhuvinov <vd@valan.net> wrote: >>> I came across a blog post of yours ( >>> http://okbob.blogspot.com/2007/11/stacked-recordset-multirecordset.html >>> ) as well as several list posts indicating that multiple result sets >>> might be in the working. Should I check the situation again when 8.4 is >>> released? > >> I have only very raw prototype, so I am sure, so this feature will not >> be in 8.4, and I am not sure about 8.5. It's nice feature, but I am >> not force to complete and clean code, and I am not able create patch. >> If you would do it, I am, with pleasure, send you source code, that >> allows multirecord sets. > > Yes, I'll be glad to examine your patch. At least to get an idea of > what's involved in implementing multiple result sets. Stored procedure support is a pretty complicated feature. They differ with functions in two major areas: *) input/output syntax. this is what you are dealing with *) manual transaction management. stored procedures should allow you emit 'BEGIN/COMMIT' and do things like vacuum. IIRC, I don't think there was a consensus on the second point or if it was ok to implement the syntax issues without worrying about transactions. I'll give you two other strategies for dealing with multiple result sets in pl/pgsql: *) temp tables: it's very easy to create/dump/drop temp tables and use them in later transactions. previous to 8.3 though, doing it this way was a pain because of plan invalidation issues. *) arrays of composites (8.2+) create table foo(a int, b int, c int); create table bar(a text, b text, c text); pl/sql: create function foobar(foos out foo[], bars out bar[]) returns record as $$ select (select array(select foo from foo)), (select array(select bar from bar)); $$ language sql; pl/pgsql: create function foobar(foos out foo[], bars out bar[]) returns record as $$ begin foos := array(select foo from foo); bars := array(select bar from bar); return; end; $$ language plpgsql; select foos[1].b from foobar(); Customize the above to taste. For example you may want to return the array dims. By the way, if you are writing client side code in C, you may want to look at libpqtypes (http://libpqtypes.esilo.com/)...it makes dealing with arrays and composites on the client sides much easier. For 8.3 though it requires a patched libpq. merlin
Hi Merlin, > Stored procedure support is a pretty complicated feature. They differ > with functions in two major areas: > > *) input/output syntax. this is what you are dealing with > *) manual transaction management. stored procedures should allow you > emit 'BEGIN/COMMIT' and do things like vacuum. > > IIRC, I don't think there was a consensus on the second point or if it > was ok to implement the syntax issues without worrying about > transactions. I understand the situation, that a range of facets such as syntax, SP i/o and the overall fit of SPs into the architecture of PG should be considered. What do the Postgres gurus say about stored procedures? My SQL experience is rather limited, but I've got the impression that every RDBMS has got its own philosophy about matters relational and I expect Posgresql to be no different. So probably an improvised hack wouldn't be of much use here and things should be thought over. Anyway, at this point I'm finished with my evaluation of Postgresql. The MySQL solution which I've got now works reasonably well. It's just that at this moment my investment into MySQL is still relatively small and I wanted to check my options before I dig myself too deeply into MySQL to make a potential sensible migration too expensive :) Maybe I'm going to revisit Postgresql again in 2009 or 2010 :) Vladimir -- Vladimir Dzhuvinov * www.valan.net * PGP key ID AC9A5C6C
Вложения
Re: PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
От
"Grzegorz Jaśkiewicz"
Дата:
On Mon, Oct 13, 2008 at 8:56 PM, Vladimir Dzhuvinov <vd@valan.net> wrote:
Maybe I'm going to revisit Postgresql again in 2009 or 2010 :)
good luck, we'll pray for your data to be safe with mysql. cos you can't trust the thing without a good prayer.
one thing, all software works differently. If you want to switch to any DBE, you have to spend more than one day on it. trust me.
> CREATE PROCEDURE list_user_accounts(IN user_id INT) > > BEGIN > > -- Return first result set (single row) > SELECT * FROM users WHERE id = user_id; > > -- Return second result set (zero or more rows) > SELECT * FROM accounts WHERE account_holder = user_id; > > END; > > > So, is it true that as of Postgresql 8.3 there is no way to have a > pgpqsql function return multiple SELECTs? > > > Vladimir Dzhuvinov > > Have you considered returning XML instead? You should be able to get what your looking for much easier with an XMLAGG. Artacus
On Mon, Oct 13, 2008 at 3:56 PM, Vladimir Dzhuvinov <vd@valan.net> wrote: > Hi Merlin, > >> Stored procedure support is a pretty complicated feature. They differ >> with functions in two major areas: >> >> *) input/output syntax. this is what you are dealing with >> *) manual transaction management. stored procedures should allow you >> emit 'BEGIN/COMMIT' and do things like vacuum. >> >> IIRC, I don't think there was a consensus on the second point or if it >> was ok to implement the syntax issues without worrying about >> transactions. > > I understand the situation, that a range of facets such as syntax, SP > i/o and the overall fit of SPs into the architecture of PG should be > considered. What do the Postgres gurus say about stored procedures? Not too much, there hasn't been a huge emphasis on getting them because we already have functions which are extremely powerful. > My SQL experience is rather limited, but I've got the impression that > every RDBMS has got its own philosophy about matters relational and I > expect Posgresql to be no different. So probably an improvised hack > wouldn't be of much use here and things should be thought over. Using temp tables inside a function isn't hacky. It was just awkward in older versions of postgresql because of limitations of the postgresql engine. > Anyway, at this point I'm finished with my evaluation of Postgresql. The > MySQL solution which I've got now works reasonably well. It's just that > at this moment my investment into MySQL is still relatively small and I > wanted to check my options before I dig myself too deeply into MySQL to > make a potential sensible migration too expensive :) If you are the type of programmer that likes to use the database as an engine to make your application development easier, you will eventually regret your decision. merlin
2008/10/14 Merlin Moncure <mmoncure@gmail.com>: > On Mon, Oct 13, 2008 at 3:56 PM, Vladimir Dzhuvinov <vd@valan.net> wrote: >> Hi Merlin, >> >>> Stored procedure support is a pretty complicated feature. They differ >>> with functions in two major areas: >>> >>> *) input/output syntax. this is what you are dealing with >>> *) manual transaction management. stored procedures should allow you >>> emit 'BEGIN/COMMIT' and do things like vacuum. >>> >>> IIRC, I don't think there was a consensus on the second point or if it >>> was ok to implement the syntax issues without worrying about >>> transactions. >> >> I understand the situation, that a range of facets such as syntax, SP >> i/o and the overall fit of SPs into the architecture of PG should be >> considered. What do the Postgres gurus say about stored procedures? > > Not too much, there hasn't been a huge emphasis on getting them > because we already have functions which are extremely powerful. > I like this functionality - but simply I am wating and searching sponsoring. It's about 2 months of work. >> My SQL experience is rather limited, but I've got the impression that >> every RDBMS has got its own philosophy about matters relational and I >> expect Posgresql to be no different. So probably an improvised hack >> wouldn't be of much use here and things should be thought over. > > Using temp tables inside a function isn't hacky. It was just awkward > in older versions of postgresql because of limitations of the > postgresql engine. with some bad impacts - creating and dropping every temp table means system tables modifications. Intensivelly using of temp tables needs intensive vacuum of system tables and hash significant negative impacts. > >> Anyway, at this point I'm finished with my evaluation of Postgresql. The >> MySQL solution which I've got now works reasonably well. It's just that >> at this moment my investment into MySQL is still relatively small and I >> wanted to check my options before I dig myself too deeply into MySQL to >> make a potential sensible migration too expensive :) > if you started on MSSQL server, then MySQL is maybe better for you. Lot of knowleages should be same. PostgreSQL is much more near Oracle or DB2, that multirecordset (if I have good knowleadges) do via cursors. > If you are the type of programmer that likes to use the database as an > engine to make your application development easier, you will > eventually regret your decision. > It's true - PostgreSQL doesn't support some important features about transactions - explicit controling of transactions, autonomous transactions, ... I hope so this functionality will be implemented in some days. Regards Pavel Stehule > merlin > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Hi guys, Ugh, why is it so hard to let go of this topic ;) 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 ;) A server API was constructed consisting of about two dozen stored procedures. The stored procedures basically encapsulated a set of INSERTs, UPDATEs and some control flow operators to perform specific tasks. So, if a user sits in front of his PC and decides to check his account balance and then make a payment to her colleague, the client software connects on her behalf to the DB and issues the following SP calls: CALL login('username', 'secret password'); -- returns session token CALL get_account_balance('session token'); CALL make_payment('session token', 'to account-ID-12345'); CALL logout('session token'); The client software makes only stored procedure calls to the DB; notice there is no direct access to tables, no BEGIN/COMMIT/ROLLBACK, etc. - all this is handled internally by the stored procedures. To enforce this protocol client connections were granted EXECUTE only; table SELECTs, UPDATEs and DELETEs are not allowed. If a stored procedure needs to return data to the client, this is done through a simple SELECT to the client (using OUT parameters would complicate interfacing). So, from a software engineering point of view, stored procedures were very good to have. But how do they relate to *functions*? Initially I wasn't quite sure why stored procedures should differ from functions, but after some thought it became clear: 1. First and foremost, they are meant to serve different purposes: 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. 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 3. (also leads from 1) Functions must have a defined return type, stored procedures normally have no such requirement. 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. 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() ). Ok, enough work for today, I'm getting a beer now :) -- Vladimir Dzhuvinov * www.valan.net * PGP key ID AC9A5C6C
Вложения
On Tue, Oct 14, 2008 at 3:45 PM, Vladimir Dzhuvinov <vd@valan.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
Re: PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
От
Ivan Sergio Borgonovo
Дата:
On Tue, 14 Oct 2008 16:51:29 -0400 "Merlin Moncure" <mmoncure@gmail.com> wrote: > 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. From a security point of view... stored procedures can't be called inside another statement making it harder to hide them for sql injection. > 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 That's one of the reasons that made me chose postgresql in spite of mysql. PostgreSQL is easier to program and its programming "infrastructure" is MUCH MUCH more mature. -- Ivan Sergio Borgonovo http://www.webthatworks.it
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
Вложения
Vladimir Dzhuvinov wrote: > > 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 :) I don't quite understand you here. I'm sure we all crave the lazy millionaire bit, but what would a lazy millionaire do other than programming for fun? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On 10/15/08, Alvaro Herrera <alvherre@commandprompt.com> wrote: > Vladimir Dzhuvinov wrote: > > > > 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 :) > > I don't quite understand you here. I'm sure we all crave the lazy > millionaire bit, but what would a lazy millionaire do other than > programming for fun? > read dozens of mails from a forum? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Wed, Oct 15, 2008 at 5:48 PM, Bruce Momjian <bruce@momjian.us> wrote: > > 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? I don't think so, except that we need a TODO for proper stored procedure support if there is not one already. Proper SPs have been much discussed, Pavel spearheading what effort has been done. Being able to manually do transactions for functions would be nice certainly, but I suspect this is a big part of the challenge for proper SPs. merlin
2008/10/16 Merlin Moncure <mmoncure@gmail.com>: > On Wed, Oct 15, 2008 at 5:48 PM, Bruce Momjian <bruce@momjian.us> wrote: >> >> 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? > > I don't think so, except that we need a TODO for proper stored > procedure support if there is not one already. Proper SPs have been > much discussed, Pavel spearheading what effort has been done. > > Being able to manually do transactions for functions would be nice > certainly, but I suspect this is a big part of the challenge for > proper SPs. > call statement should to live outside implicit transaction, so it's possible. It's simple in SQL/PSM, that is designed with transaction controll management. Pavel > merlin > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >