Thread: cannot use result of (insert .. returning)

cannot use result of (insert .. returning)

From

"Dale Harris"

Date:

14 August 2008, 06:12:29

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.

Re: cannot use result of (insert .. returning)

From

"Dale Harris"

Date:

14 August 2008, 07:21:37

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 would ideally like to be able to capture the RETURNING value into a variable to use immediately. Does anyone have a solution?

Dale.

Re: cannot use result of (insert .. returning)

From

"Pavel Stehule"

Date:

14 August 2008, 07:59:33

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>:
> 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.

Re: cannot use result of (insert .. returning)

From

"Dale Harris"

Date:

15 August 2008, 00:44:38

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();

---

(0 rows)

regards

Pavel Stehule

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

> 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.