Thread: BUG #18587: PLPGSQL : conflict between variable and ON CONFLICT (a_column)

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

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.

thanks for the answer David.
Remi