Thread: PGSQL returning into in insert statement
Hi all,
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".
What is the correct method to use it?
Thanks!
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
Understood. Thank you!
On Thu, 30 Jul 2020, 08:35 Tom Lane, <tgl@sss.pgh.pa.us> wrote:
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
Btw oracle support this in plain sql too.
On Thu, 30 Jul 2020, 11:42 Chamath Sajeewa, <csgsajeewa@gmail.com> wrote:
Understood. Thank you!On Thu, 30 Jul 2020, 08:35 Tom Lane, <tgl@sss.pgh.pa.us> wrote: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