public inbox for [email protected]
help / color / mirror / Atom feedPGSQL returning into in insert statement
5+ messages / 3 participants
[nested] [flat]
* PGSQL returning into in insert statement
@ 2020-07-30 02:46 Chamath Sajeewa <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Chamath Sajeewa @ 2020-07-30 02:46 UTC (permalink / raw)
To: [email protected]
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!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PGSQL returning into in insert statement
@ 2020-07-30 03:05 Tom Lane <[email protected]>
parent: Chamath Sajeewa <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2020-07-30 03:05 UTC (permalink / raw)
To: Chamath Sajeewa <[email protected]>; +Cc: [email protected]
Chamath Sajeewa <[email protected]> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PGSQL returning into in insert statement
@ 2020-07-30 06:12 Chamath Sajeewa <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Chamath Sajeewa @ 2020-07-30 06:12 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: [email protected]
Understood. Thank you!
On Thu, 30 Jul 2020, 08:35 Tom Lane, <[email protected]> wrote:
> Chamath Sajeewa <[email protected]> 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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PGSQL returning into in insert statement
@ 2020-07-30 06:56 Chamath Sajeewa <[email protected]>
parent: Chamath Sajeewa <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Chamath Sajeewa @ 2020-07-30 06:56 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: [email protected]
Btw oracle support this in plain sql too.
On Thu, 30 Jul 2020, 11:42 Chamath Sajeewa, <[email protected]> wrote:
> Understood. Thank you!
>
> On Thu, 30 Jul 2020, 08:35 Tom Lane, <[email protected]> wrote:
>
>> Chamath Sajeewa <[email protected]> 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
>>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PGSQL returning into in insert statement
@ 2020-07-30 14:36 Stephen Frost <[email protected]>
parent: Chamath Sajeewa <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Stephen Frost @ 2020-07-30 14:36 UTC (permalink / raw)
To: Chamath Sajeewa <[email protected]>; +Cc: Tom Lane <[email protected]>; [email protected]
Greetings,
* Chamath Sajeewa ([email protected]) 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
Attachments:
[application/pgp-signature] signature.asc (819B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2020-07-30 14:36 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-07-30 02:46 PGSQL returning into in insert statement Chamath Sajeewa <[email protected]>
2020-07-30 03:05 ` Tom Lane <[email protected]>
2020-07-30 06:12 ` Chamath Sajeewa <[email protected]>
2020-07-30 06:56 ` Chamath Sajeewa <[email protected]>
2020-07-30 14:36 ` Stephen Frost <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox