Thread: 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
Forgot to mention: using 8.3.3 on FreeBSD.
"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
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 :)
On 04/11/2008 01:20, Diego Schulz wrote: > I also tried this (somewhat silly) syntax to circumvent the issue > without resorting in currval: Just curious - what have you got against currval()? It seems to me that it would make your life easier.... Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
On Mon, Nov 3, 2008 at 10:24 PM, Raymond O'Donnell <rod@iol.ie> wrote: > On 04/11/2008 01:20, Diego Schulz wrote: > >> I also tried this (somewhat silly) syntax to circumvent the issue >> without resorting in currval: > > Just curious - what have you got against currval()? It seems to me that > it would make your life easier.... > > Ray. > > > ------------------------------------------------------------------ > Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland > rod@iol.ie > Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals > ------------------------------------------------------------------ > I simply don't like having to cast from BIGINT to INTEGER, as currval returns BIGINT while the index of my table is INTEGER. I think isn't as readable and elegant as the single INSERT ... RETURNING value. Being the only choice at this time (that I'm aware of) I'm using something like this: SELECT CAST(CURRVAL('mytable_id_seq') AS INTEGER); If I can avoid messing with sequence manipulation functions, surely I will. Cheers!
"Diego Schulz" <dschulz@gmail.com> writes:
> On Mon, Nov 3, 2008 at 10:24 PM, Raymond O'Donnell <rod@iol.ie> wrote:
>> Just curious - what have you got against currval()? It seems to me that
>> it would make your life easier....
> I simply don't like having to cast from BIGINT to INTEGER,
Under what circumstances do you need an explicit cast?
regards, tom lane
On Tue, Nov 4, 2008 at 2:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Diego Schulz" <dschulz@gmail.com> writes: >> On Mon, Nov 3, 2008 at 10:24 PM, Raymond O'Donnell <rod@iol.ie> wrote: >>> Just curious - what have you got against currval()? It seems to me that >>> it would make your life easier.... > >> I simply don't like having to cast from BIGINT to INTEGER, > > Under what circumstances do you need an explicit cast? > > regards, tom lane > When I want the function to return the same type as the index of the table (normally SERIAL), and I have other functions that rely on the datatype returned. To avoid casting I can simply change the function's signature to return BIGINT (to match currval() return type) and the problem vanishes but.. then I have more functions that needs to be adapted. Maybe I'm a bit paranoid of BIGINT's performance penalty too, as the set of functions will be heavily used, but honestly, this fear completely lacks foundation. Just to make it clear, the main reason for this thread was curiosity :) Thank you for your time.
> 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?
On Tue, Nov 4, 2008 at 9:57 AM, Lennin Caro <lennin.caro@yahoo.com> wrote: >> 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? > > I'm using 8.3.3.
you can try: CREATE OR REPLACE FUNCTION add_something(text, text) RETURNS integer AS $BODY$ DECLARE somevariable integer; BEGIN INSERT INTO sometable (id, foo, bar ) VALUES (DEFAULT, $1, $2 ) RETURNING id INTO somevariable; return somevariable; END;$BODY$ LANGUAGE 'plpgsql' VOLATILE; -- View this message in context: http://www.nabble.com/INSERT-with-RETURNING-clause-inside-SQL-function-tp20312197p25678362.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
(k) -- View this message in context: http://postgresql.1045698.n5.nabble.com/INSERT-with-RETURNING-clause-inside-SQL-function-tp1923653p5445810.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.