Re: INSERT with RETURNING clause inside SQL function

On Mon, Nov 3, 2008 at 8:51 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Diego Schulz" <dschulz(at)gmail(dot)com> writes:
>> I expected something like this to work, but it doesn't:
>
>> 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 ;
>
> This case was implemented last week. In existing release branches
> you'll need to use currval or some other workaround to collect the
> serial value.
>
> regards, tom lane
>

Thank you Tom. Happy to read it's implemented now! :)

After re-reading the docs:

"...the final command _must be a SELECT_ that returns whatever
is specified as the function's return type"

I also tried this (somewhat silly) syntax to circumvent the issue
without resorting in currval:

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

and

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

As expected, none of them works as *I* expected.
You know, fools keep trying.. and eventually hit :)