Thread: insert ..... returning problem
Greetings!
I'm trying to insert a record returning column value using the following query:
INSERT INTO t_load (load_id, load_type) VALUES (:id, :typ) returning load_idx INTO :idx
But when I try to prepare the query I'm getting the following error:
PREPARE p1515544c00000001 AS insert into t_load (load_id, load_type) values ($1, $2) returning load_idx into $3]:
ERROR: syntax error at or near "into" LINE 1: ...ad_id, load_type) values ($1, $2) returning load_idx into $3
^
Why 'into var' is not accepted here?
Best regards,
--
\ / | |
(OvO) | Mikhail Iwanow |
(^^^) | Voice: +7 (911) 223-1300 |
\^/ | E-mail: ivans@logit-ag.de |
^ ^ | |
On Thu, 2021-06-24 at 12:31 +0300, Michael Ivanov wrote: > I'm trying to insert a record returning column value using the following query: > > INSERT INTO t_load (load_id, load_type) VALUES (:id, :typ) returning load_idx INTO :idx > > But when I try to prepare the query I'm getting the following error: > > PREPARE p1515544c00000001 AS insert into t_load (load_id, load_type) values ($1, $2) returning load_idx into $3]: > ERROR: syntax error at or near "into" LINE 1: ...ad_id, load_type) values ($1, $2) returning load_idx into $3 > ^ > Why 'into var' is not accepted here? Are you talking about ECPG? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Hallo, no. I am using postgres c library, namely I'm calling PQexec() Best regards, On 24.06.2021 16:14, Laurenz Albe wrote: > On Thu, 2021-06-24 at 12:31 +0300, Michael Ivanov wrote: >> I'm trying to insert a record returning column value using the following query: >> >> INSERT INTO t_load (load_id, load_type) VALUES (:id, :typ) returning load_idx INTO :idx >> >> But when I try to prepare the query I'm getting the following error: >> >> PREPARE p1515544c00000001 AS insert into t_load (load_id, load_type) values ($1, $2) returning load_idx into $3]: >> ERROR: syntax error at or near "into" LINE 1: ...ad_id, load_type) values ($1, $2) returning load_idx into $3 >> ^ >> Why 'into var' is not accepted here? > > Are you talking about ECPG? > > Yours, > Laurenz Albe > -- \ / | | (OvO) | Михаил Иванов | (^^^) | | \^/ | E-mail: ivans@isle.spb.ru | ^ ^ | |
On Thu, 24 Jun 2021 at 15:25, Michael Ivanov <ivans@isle.spb.ru> wrote:
Hallo, no.
I am using postgres c library, namely I'm calling PQexec()
Best regards,
On 24.06.2021 16:14, Laurenz Albe wrote:
> On Thu, 2021-06-24 at 12:31 +0300, Michael Ivanov wrote:
>> I'm trying to insert a record returning column value using the following query:
>>
>> INSERT INTO t_load (load_id, load_type) VALUES (:id, :typ) returning load_idx INTO :idx
>>
>> But when I try to prepare the query I'm getting the following error:
>>
>> PREPARE p1515544c00000001 AS insert into t_load (load_id, load_type) values ($1, $2) returning load_idx into $3]:
>> ERROR: syntax error at or near "into" LINE 1: ...ad_id, load_type) values ($1, $2) returning load_idx into $3
>> ^
>> Why 'into var' is not accepted here?
>
> Are you talking about ECPG?
>
> Yours,
> Laurenz Albe
>
--
\ / | |
(OvO) | Михаил Иванов |
(^^^) | |
\^/ | E-mail: ivans@isle.spb.ru |
^ ^ | |
Hi,
Client libraries substitute the values(!) of the variables into the sql queries (Or in this case do a PREPARE and provide the values to that), but the final query must comply with Postgres syntax.
You can get the values defined in the returning clause from the result set, like you would do in the case of a SELECT.
Regards,
Sándor
On Thu, 2021-06-24 at 16:25 +0300, Michael Ivanov wrote: > > > I'm trying to insert a record returning column value using the following query: > > > > > > INSERT INTO t_load (load_id, load_type) VALUES (:id, :typ) returning load_idx INTO :idx > > > > > > But when I try to prepare the query I'm getting the following error: > > > > > > PREPARE p1515544c00000001 AS insert into t_load (load_id, load_type) values ($1, $2) returning load_idx into $3]: > > > ERROR: syntax error at or near "into" LINE 1: ...ad_id, load_type) values ($1, $2) returning load_idx into$3 > > > ^ > > > Why 'into var' is not accepted here? > > > > Are you talking about ECPG? > > I am using postgres c library, namely I'm calling PQexec() Then you don't need it. You may be mixing up SQL syntax and PL/pgSQL syntax. Just run the statement without the INTO, and it will return a result set, just like a SELECT would. By the way, you cannot have parameters with PQexec(). Use PQexecParams() for that. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
I was just trying to use it similar to oracle handling in "returning COLUMN into :VAR" So in postgres I should not explicitly specify destination variable for 'returning' value in query itself, but rather retrieve the result from PGresult returned by PQexec(). ok, thanks for clarification! On 24.06.2021 17:13, Laurenz Albe wrote: > On Thu, 2021-06-24 at 16:25 +0300, Michael Ivanov wrote: >>>> I'm trying to insert a record returning column value using the following query: >>>> >>>> INSERT INTO t_load (load_id, load_type) VALUES (:id, :typ) returning load_idx INTO :idx >>>> >>>> But when I try to prepare the query I'm getting the following error: >>>> >>>> PREPARE p1515544c00000001 AS insert into t_load (load_id, load_type) values ($1, $2) returning load_idx into $3]: >>>> ERROR: syntax error at or near "into" LINE 1: ...ad_id, load_type) values ($1, $2) returning load_idx into $3 >>>> ^ >>>> Why 'into var' is not accepted here? >>> >>> Are you talking about ECPG? >> >> I am using postgres c library, namely I'm calling PQexec() > > Then you don't need it. You may be mixing up SQL syntax and PL/pgSQL syntax. > > Just run the statement without the INTO, and it will return a result set, just > like a SELECT would. > > By the way, you cannot have parameters with PQexec(). > Use PQexecParams() for that. > > Yours, > Laurenz Albe > -- \ / | | (OvO) | Михаил Иванов | (^^^) | | \^/ | E-mail: ivans@isle.spb.ru | ^ ^ | |