Re: PGSQL returning into in insert statement

Greetings,

* Chamath Sajeewa (csgsajeewa(at)gmail(dot)com) wrote:
> Btw oracle support this in plain sql too.

Please don't top-post.

The question here, it would seem, is- where do you expect that
returnvalue to go? If you want it to be returned to you (making the
INSERT look like a SELECT) then you can just say 'returning value;' and
you don't need to do anything else.

If you want to set a server-side variable with the result, you could do
something like:

=*> with myinsert as
(insert into test_table(key,value) values ('key1',5) returning value)
select set_config('myvar.value'::text,myinsert.value::text,true)
from myinsert;
set_config
------------
5
(1 row)

Which you can then query with:

=*> select current_setting('myvar.value');
current_setting
-----------------
5
(1 row)

Thanks,

Stephen