pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18587: PLPGSQL : conflict between variable and ON CONFLICT (a_column)
3+ messages / 3 participants
[nested] [flat]
* BUG #18587: PLPGSQL : conflict between variable and ON CONFLICT (a_column)
@ 2024-08-21 12:34 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2024-08-21 12:34 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: remi.cura@gmail.com
The following bug has been logged on the website:
Bug reference: 18587
Logged by: Rémi Cura
Email address: remi.cura@gmail.com
PostgreSQL version: 16.3
Operating system: Linux
Description:
Writing a PLPGSQL function returning a table with a column named id, and
using the same column name id in a INSERT ON CONFLICT (id) makes plpgsql
error.
I know there is a name collision here, but PLPGSQL should not try to
subsitute any variable in the `ON CONFLICT ("something")` in the first
place, as in SQL the only allowed value for "something" are column names
(not variables, expressions, etc).
The only workaround I found is to use `#variable_conflict use_column`, which
is bad practice and confusing imo, and makes the code less portable and
maintainable.
I wrote this to reproduce:
```SQL
CREATE TABLE IF NOT EXISTS public.test_plpgsql_bug(
id bigint PRIMARY KEY ,
payload text
);
INSERT INTO public.test_plpgsql_bug VALUES (1,'payload 1'),(2,'payload
2');
CREATE OR REPLACE FUNCTION public.test_plpgsql_bug(_new_id bigint,
_new_payload text)
RETURNS TABLE (id bigint, payload text)
LANGUAGE PLPGSQL
VOLATILE
AS $fun_test$
BEGIN
RETURN QUERY
WITH inserting aS (
INSERT INTO public.test_plpgsql_bug as t ( id, payload)
SELECT _new_id, _new_payload
ON CONFLICT (id)
DO UPDATE SET payload=EXCLUDED.payload
RETURNING t.id, t.payload
)
SELECT i.id, i.payload
FROM inserting i ;
END ;
$fun_test$;
-- running the function creates an error
SELECT *
FROM public.test_plpgsql_bug(_new_id := 1, _new_payload := 'new payload 1 ')
;
--SQL Error [42702]: ERROR: column reference "id" is ambiguous
-- Detail: It could refer to either a PL/pgSQL variable or a table
column.
-- clean
DROP TABLE IF EXISTS public.test_plpgsql_bug;
DROP FUNCTION IF EXISTS public.test_plpgsql_bug ;
```
I don't think the pg version is relevant here, but here it is in full:
PostgreSQL 16.3 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.1
20180712 (Red Hat 7.3.1-6), 64-bit
Thank you very much dear community!
Remi
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18587: PLPGSQL : conflict between variable and ON CONFLICT (a_column)
@ 2024-08-21 13:17 David G. Johnston <david.g.johnston@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: David G. Johnston @ 2024-08-21 13:17 UTC (permalink / raw)
To: remi.cura@gmail.com <remi.cura@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Wednesday, August 21, 2024, PG Bug reporting form <noreply@postgresql.org>
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 18587
> Logged by: Rémi Cura
> Email address: remi.cura@gmail.com
> PostgreSQL version: 16.3
> Operating system: Linux
> Description:
>
> Writing a PLPGSQL function returning a table with a column named id, and
> using the same column name id in a INSERT ON CONFLICT (id) makes plpgsql
> error.
>
See prior discussions.
https://www.postgresql.org/message-id/CAKFQuwYZymuC8%3DEtoFRoGM8U79HQArJNWwA8i-JCZRGomkOkdg%40mail.g...
In short, the benefit-to-cost ratio to change things here is very low so
it’s likewise a very low priority for everyone who write patches (and
probably needs a fairly experienced hacker too).
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18587: PLPGSQL : conflict between variable and ON CONFLICT (a_column)
@ 2024-08-21 13:30 Rémi Cura <remi.cura@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Rémi Cura @ 2024-08-21 13:30 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
thanks for the answer David.
Remi
On Wed, Aug 21, 2024, 15:17 David G. Johnston <david.g.johnston@gmail.com>
wrote:
> On Wednesday, August 21, 2024, PG Bug reporting form <
> noreply@postgresql.org> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference: 18587
>> Logged by: Rémi Cura
>> Email address: remi.cura@gmail.com
>> PostgreSQL version: 16.3
>> Operating system: Linux
>> Description:
>>
>> Writing a PLPGSQL function returning a table with a column named id, and
>> using the same column name id in a INSERT ON CONFLICT (id) makes plpgsql
>> error.
>>
>
> See prior discussions.
>
>
> https://www.postgresql.org/message-id/CAKFQuwYZymuC8%3DEtoFRoGM8U79HQArJNWwA8i-JCZRGomkOkdg%40mail.g...
>
> In short, the benefit-to-cost ratio to change things here is very low so
> it’s likewise a very low priority for everyone who write patches (and
> probably needs a fairly experienced hacker too).
>
> David J.
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-08-21 13:30 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-08-21 12:34 BUG #18587: PLPGSQL : conflict between variable and ON CONFLICT (a_column) PG Bug reporting form <noreply@postgresql.org>
2024-08-21 13:17 ` David G. Johnston <david.g.johnston@gmail.com>
2024-08-21 13:30 ` Rémi Cura <remi.cura@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox