On Mon, Nov 3, 2008 at 8:51 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
> "Diego Schulz" <dschulz@xxxxxxxxx> 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 :)
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
