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 return 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 logic equivalent
to (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