Re: [GENERAL] INSERT with RETURNING clause inside SQL function
(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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT with RETURNING clause inside SQL function
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT with RETURNING clause inside SQL function
On Tue, Nov 4, 2008 at 9:57 AM, Lennin Caro [EMAIL PROTECTED] 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT with RETURNING clause inside SQL function
On Tue, Nov 4, 2008 at 2:38 AM, Tom Lane [EMAIL PROTECTED] wrote: Diego Schulz [EMAIL PROTECTED] writes: On Mon, Nov 3, 2008 at 10:24 PM, Raymond O'Donnell [EMAIL PROTECTED] 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT with RETURNING clause inside SQL function
Forgot to mention: using 8.3.3 on FreeBSD. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT with RETURNING clause inside SQL function
Diego Schulz [EMAIL PROTECTED] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT with RETURNING clause inside SQL function
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 [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT with RETURNING clause inside SQL function
On Mon, Nov 3, 2008 at 10:24 PM, Raymond O'Donnell [EMAIL PROTECTED] 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
[EMAIL PROTECTED]
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!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT with RETURNING clause inside SQL function
On Mon, Nov 3, 2008 at 8:51 PM, Tom Lane [EMAIL PROTECTED] wrote: Diego Schulz [EMAIL PROTECTED] 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT with RETURNING clause inside SQL function
Diego Schulz [EMAIL PROTECTED] writes: On Mon, Nov 3, 2008 at 10:24 PM, Raymond O'Donnell [EMAIL PROTECTED] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
