INSERT with RETURNING clause inside SQL function

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.