INSERT with RETURNING clause inside SQL function

["Diego Schulz" <dschulz@xxxxxxxxx>]

Hi all,

I'm re-writing some functions and migrating bussines logic from a
client application to PostgreSQL.

I expected something like this to work, but it doesn't:

-- simple table
CREATE TABLE sometable (
   id SERIAL PRIMARY KEY,
   text1 text,
   text2 text
);

CREATE OR REPLACE FUNCTION add_something(text, text) RETURNS INTEGER AS $$
   INSERT INTO sometable (id, foo, bar ) VALUES (DEFAULT, $1, $2 )
RETURNING id ;
$$ LANGUAGE SQL ;


Please note the use of RETURNING clause. If I put a SELECT 1; after
the INSERT, the function works (but doesn't returns any useful value
:)
I need the function to return the last insert id. And yes, I'm aware
that the same can be achieved by selecting the greatest id in the
SERIAL secuence, but is not as readable as RETURNING syntax. And no,
for me it's not important that RETURNING is not standard SQL.

Does anyone knows why RETURNING doesn't works inside SQL functions?

Any advise will be very appreciated. TIA.

diego

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general