We have been generally very satisfied with the PostgreSQL feature list. Data types looked OK, referential integrity was
alsothere and we liked the inheritance too ("finally somebody agrees with us" - we thought). We got very excited about
theLIMIT/OFFSET feature with SELECT statement; think about a typical Internet search. Only two transaction isolation
levels?Well, no problem, really: dirty reads are sometimes pain in the ass anyway, we will survive without them.
Triggersare there, thanks God. PL/pgSQL? Superb, sounds like good old PL/SQL, let's dive into it a bit....
... and then we discovered that there is no way to return output variables from stored procedures il PL/pgSQL.
Actually,there are no stored procedures at all - just some (not very useful) functions. Not being able to return
severalvalues in one procedure call from a relational database sounded extreemly immature to us. Here is why:
We do program a lot on the server side; in fact, our client applications are only allowed to display data (using
SELECT)and to call procedures. They are simply not allowed to do INSERTS, UPDATES or DELETES on their own. That's why
parametertransfer and stored procs are so important to us.
Here is a typical, though simplified, TransactSQL example that our client applications often execute using the returned
variablescorrectly. However, we have no idea how to port this proc to PL/pgSQL, despite "workarounds" proposed in the
documentation:
-- Inserting Cities into the database
CREATE PROCEDURE sp_City_INS
@ID INT OUT,
@Name char(20),
@Country char(2) OUT
AS
BEGIN
-- we live in Norway by default
if @Country is null
select @Country = 'N'
-- INSERTING a City record.
-- Note the absence of the ID field which is automatically generated.
-- We have to store the newly generated ID not to forget it:
insert into City(Name, Country) values(@Name, @Country)
select @ID=@@identity
END
Now, @ID and @Country are being returned to the caller application through the network as output params. This also
meansthat the database driver must be familiar with the concept of stored procedures and parameter transfer. This
cannotbe the case with a PostgreSQL driver - there are no stored proc there at all.
We would be extreemly hapy if you guys prove us wrong - we actually loved PostgreSQL, indeed. Are you able to do that?
