Re: INSERT with RETURNING clause inside SQL function

On Mon, Nov 3, 2008 at 8:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Diego Schulz" <dschulz@gmail.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 :)