plpgsql ON CONFLICT clause creates ambiguous col reference with returns table

From: Bill MacArthur <bill(at)deriv(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: plpgsql ON CONFLICT clause creates ambiguous col reference with returns table
Date: 2021-11-02 23:38:58
Message-ID: CAEDVVWvqaRd0u4rJBaaHquJ+ipwm1QFyT7StW91HTVpSKvw5KA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Browse pgsql-bugs by date

  From Date Subject
Next Message Peter Geoghegan 2021-11-02 23:48:03 Re: BUG #17245: Index corruption involving deduplicated entries
Previous Message PG Bug reporting form 2021-11-02 18:59:12 BUG #17262: "View manual" button on postgres.org/docs is overflowing horizontally on mobile view