plpgsql ON CONFLICT clause creates ambiguous col reference with returns table

Поиск
Список
Период
Сортировка
От Bill MacArthur
Тема plpgsql ON CONFLICT clause creates ambiguous col reference with returns table
Дата
Msg-id CAEDVVWvqaRd0u4rJBaaHquJ+ipwm1QFyT7StW91HTVpSKvw5KA@mail.gmail.com
обсуждение исходный текст
Список 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



В списке pgsql-bugs по дате отправления:

Предыдущее
От: PG Bug reporting form
Дата:
Сообщение: BUG #17262: "View manual" button on postgres.org/docs is overflowing horizontally on mobile view
Следующее
От: Peter Geoghegan
Дата:
Сообщение: Re: BUG #17245: Index corruption involving deduplicated entries