BUG #18587: PLPGSQL : conflict between variable and ON CONFLICT (a_column)
От | PG Bug reporting form |
---|---|
Тема | BUG #18587: PLPGSQL : conflict between variable and ON CONFLICT (a_column) |
Дата | |
Msg-id | 18587-c4ee4d43f6a4f8f3@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #18587: PLPGSQL : conflict between variable and ON CONFLICT (a_column)
|
Список | pgsql-bugs |
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
В списке pgsql-bugs по дате отправления: