Re: INSERT with RETURNING clause inside SQL function

> 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
>
Hi.. what version of postgres you have?