Re: PGSQL returning into in insert statement - Mailing list pgsql-novice

Greetings,

* Chamath Sajeewa (csgsajeewa@gmail.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