PGSQL returning into in insert statement

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

PGSQL returning into in insert statement

Chamath Sajeewa
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!
Reply | Threaded
Open this post in threaded view
|

Re: PGSQL returning into in insert statement

Tom Lane-2
Chamath Sajeewa <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: PGSQL returning into in insert statement

Chamath Sajeewa
Understood. Thank you!

On Thu, 30 Jul 2020, 08:35 Tom Lane, <[hidden email]> wrote:
Chamath Sajeewa <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: PGSQL returning into in insert statement

Chamath Sajeewa
Btw oracle support this in plain sql too.

On Thu, 30 Jul 2020, 11:42 Chamath Sajeewa, <[hidden email]> wrote:
Understood. Thank you!

On Thu, 30 Jul 2020, 08:35 Tom Lane, <[hidden email]> wrote:
Chamath Sajeewa <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: PGSQL returning into in insert statement

Stephen Frost
Greetings,

* Chamath Sajeewa ([hidden email]) 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

signature.asc (836 bytes) Download Attachment