Re: [HACKERS] Priorities for 6.6
От | Kristofer Munn |
---|---|
Тема | Re: [HACKERS] Priorities for 6.6 |
Дата | |
Msg-id | Pine.LNX.4.04.9906061424540.10982-100000@dec.munn.com обсуждение исходный текст |
Список | pgsql-hackers |
I've been following this thread via Usenet and wanted to weigh in as a large application developer using Postgresql on Linux. I want to thank everyone for their wonderful work on getting the database to where it is today. I haven't seen the following discussed at length anywhere and hopefully it's not because I've overlooked some key functionality that already does this... With Postgresql, the biggest handicap I run into in day-to-day development (as compared to the numerous other database I deal with) is the lack of stored procedures that can return multiple rows (a la Sybase, MS SQL, etc). These procedures are pre-compiled (pre-planned) and would execute faster than feeding the commands to the parser one at a time and performing any conditional logic on the front-end. The ability to store the equivalent of (please forgive any syntax mistakes)... IF EXISTS(SELECT 1 FROM tblFoo where checkFlag = 1) SELECT col1, col2, col3 FROM tblFoo where checkFlag = 1 ELSE BEGIN SELECT 'No rows were found.' AS errorStr RETURN -1 END RETURN 0 or BEGIN TRANSACTION IF EXISTS(SELECT 1 FROM tblFoo WHERE uniqueID = @idparam) BEGINUPDATE tblFoo SET col1 = @col1, col2 = @col2 WHERE uniqueID = @idparam IF @error != 0BEGIN ROLLBACK TRANSACTION SELECT "Unable to update record." AS errorStr RETURN -1END END ELSE BEGININSERT INTO tblFoo (col1, col2) VALUES (@col1, @col2) IF @error != 0BEGIN ROLLBACK TRANSACTION SELECT "Unable to insert record." AS errorStr RETURN -1END SELECT @idparam = @@identity END COMMIT TRANSACTION SELECT @idparam AS rowID RETURN 0 into some sort of compiled procedure at the database would be tremendously useful. Plus, most of the execution time for some multiway joins seems to be spent on the planning of the command, not the actual doing - time which can be recaptured by compiling a procedure once (and perhaps after every VACUUM ANALYZE). The procedures would also help developers centralize their code across platforms. My application runs PHP for the web interface and Perl for behind-the-scenes processing. I have to re-implement/re-write the same code to do the same thing - once under Perl and once under PHP and maintain both. With stored procedures that return multiple rows, I could simply put that code in the database and simplify my PHP and Perl code by an order of magnitude (not a trivial thing for interpreted languages). Finally, implementing stored procedures using the same language constructs as MS SQL and Sybase (virtually identical) would allow existing developers to write new applications and/or port existing applications to Postgresql. Even if we weren't to add the same language but simply add the ability to return multiple rows to the existing stored function languages, it would be a giant step forward for myself and other application developers. Thanks for your time... - K Kristofer Munn * http://www.munn.com/~kmunn/ * ICQ# 352499 * AIM: KrMunn
В списке pgsql-hackers по дате отправления: