Обсуждение: PGSQL returning into in insert statement

Chamath Sajeewa <csgsajeewa@gmail.com> writes:
> I want to use "returning into" in insert statement. I tried below,

> insert into test_table(key,value) values('key1',5) returning value into
> returnvalue;

> and below error occurred- syntax error at or near "into".

That is perfectly legit syntax within a plpgsql function, but it
would produce the mentioned syntax error if you tried to use it
directly at the SQL level.  So I conclude that you're fuzzy about
the difference between SQL and plpgsql.  You can use plpgsql
statements within plpgsql-language function definitions, and
within DO blocks.  But not as plain SQL.  (Plain SQL has no concept
of variables either, so "into returnvalue" makes no sense there
anyway.)

            regards, tom lane

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