We have been generally very satisfied with the PostgreSQL feature list. Data types looked OK, referential integrity was also there and we liked the inheritance too ("finally somebody agrees with us" - we thought). We got very excited about the LIMIT/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. Triggers are 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 several values 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 parameter transfer 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 variables correctly. 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 means that the database driver must be familiar with the concept of stored procedures and parameter transfer. This cannot be 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?