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