Re: [HACKERS] Priorities for 6.6

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)
BEGIN
UPDATE tblFoo SET col1 = @col1, col2 = @col2
WHERE uniqueID = @idparam

IF @error != 0
BEGIN
ROLLBACK TRANSACTION
SELECT "Unable to update record." AS errorStr
RETURN -1
END
END
ELSE BEGIN
INSERT INTO tblFoo (col1, col2) VALUES (@col1, @col2)

IF @error != 0
BEGIN
ROLLBACK TRANSACTION
SELECT "Unable to insert record." AS errorStr
RETURN -1
END

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