Thread: plpgsql ON CONFLICT clause creates ambiguous col reference with returns table
plpgsql ON CONFLICT clause creates ambiguous col reference with returns table
From
Bill MacArthur
Date:
When creating a plpgsql function that uses RETURNS TABLE -and- a RETURNS QUERY statement that uses ON CONFLICT (with like named columns), the run-time error is reported "ERROR: column reference "my_colname" is ambiguous" This happens on pg 9.6 and 14. Did not test versions between. The "ambiguous" error is understandable in other contexts, but in this case the overlap cannot be rectified by using fully qualified column names because ON CONFLICT doesn't digest them, resulting in a compile time error. Here is some sample code: psql -X -p 5440 psql (14.0 (Debian 14.0-1.pgdg90+1)) create table x(id integer primary key); create or replace function ux(v integer) returns table (id integer) language plpgsql VOLATILE as $$ begin return query with d AS (insert into x values(v) on conflict(id) do nothing returning x.id) select * from d; return; end; $$; The error: select ux(1); ERROR: column reference "id" is ambiguous LINE 1: with d AS (insert into x values(v) on conflict(id) do nothin... ^ DETAIL: It could refer to either a PL/pgSQL variable or a table column. QUERY: with d AS (insert into x values(v) on conflict(id) do nothing returning x.id) select * from d CONTEXT: PL/pgSQL function ux(integer) line 3 at RETURN QUERY Trying FQN create or replace function ux(v integer) returns table (id integer) language plpgsql VOLATILE as $$ begin return query with d AS (insert into x values(v) on conflict(x.id) do nothing returning x.id) select * from d; return; end; $$; ERROR: syntax error at or near ")" LINE 3: ...ith d AS (insert into x values(v) on conflict(x.id) do nothi... ^ Postgres verson packages: postgresql-client-14 14.0-1.pgdg90+1 amd64 postgresql-14 14.0-1.pgdg90+1 amd64 postgresql-14-pglogical 2.4.0-1.pgdg90+1 amd64 postgresql-14-pgtap 1.1.0-5.pgdg90+1 all postgresql-client-9.6 9.6.22-0+deb9u1 amd64 postgresql-client-common 231.pgdg90+1 all postgresql-common 231.pgdg90+1 all postgresql-contrib-9.6 9.6.22-1.pgdg90+1 amd64 OS PRETTY_NAME="Debian GNU/Linux 9 (stretch)" NAME="Debian GNU/Linux" VERSION_ID="9" VERSION="9 (stretch)" VERSION_CODENAME=stretch