From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | remi(dot)cura(at)gmail(dot)com |
Subject: | BUG #18587: PLPGSQL : conflict between variable and ON CONFLICT (a_column) |
Date: | 2024-08-21 12:34:37 |
Message-ID: | 18587-c4ee4d43f6a4f8f3@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 18587
Logged by: Rémi Cura
Email address: remi(dot)cura(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2024-08-21 13:17:32 | Re: BUG #18587: PLPGSQL : conflict between variable and ON CONFLICT (a_column) |
Previous Message | PG Bug reporting form | 2024-08-21 11:53:29 | BUG #18586: Process (and transaction) is stuck in IPC when the DB is under high load |