cannot use result of (insert .. returning)

I've found my solution as in the help file under "RETURNING INTO". It would
be nice if this was referenced on the INSERT documentation.

Dale

From: pgsql-general-owner@postgresql.org

Sent: Thursday, 14 August 2008 15:32
To: pgsql-general@postgresql.org
Subject: [GENERAL] cannot use result of (insert .. returning)

Hello,

I'm having the same issues as dvs had in message thread
http://archives.postgresql.org/pgsql-general/2008-05/msg01117.php as I want
to be able to use the result from an INSERT INTO table(...) VALUES(...)
RETURNING new_row_ID.

I would ideally like to be able to capture the RETURNING value into a
variable to use immediately. Does anyone have a solution?

Dale.

Hello

you can wrap INSERT STATEMENT into function. Than you can do anything
with result;

create table f(a timestamp);

postgres=# select * from (insert into f values(current_timestamp)
returning *) x where x.a > now();
ERROR: syntax error at or near "into"
LINE 1: select * from (insert into f values(current_timestamp) retur...
^
create or replace function if() returns setof f as $$begin return
query insert into f values(current_timestamp) returning *; return;
end$$ language plpgsql;

postgres=# select * from if() where a > now();
a
---
(0 rows)

regards
Pavel Stehule

2008/8/14 Dale Harris <itsupport@jonkers.com.au>:

Hi Pavel,

Thank you for your reply, but in this case the “INSERT INTO ... RETURNING field,... INTO STRICT variable,...;” is what works best for me currently.

Regards,

Dale Harris

-----Original Message-----
From: Pavel Stehule [mailto:pavel.stehule@gmail.com]
Sent: Thursday, 14 August 2008 17:59
To: Dale Harris
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] cannot use result of (insert .. returning)

Hello

you can wrap INSERT STATEMENT into function. Than you can do anything

with result;

create table f(a timestamp);

postgres=# select * from (insert into f values(current_timestamp)

returning *) x where x.a > now();

ERROR: syntax error at or near "into"

LINE 1: select * from (insert into f values(current_timestamp) retur...

^

create or replace function if() returns setof f as $$begin return

query insert into f values(current_timestamp) returning *; return;

end$$ language plpgsql;

postgres=# select * from if() where a > now();

a

---

(0 rows)

regards

Pavel Stehule

2008/8/14 Dale Harris <itsupport@jonkers.com.au>: