plpgsql: ambiguous column reference in ON CONFLICT clause

From: Torsten Förtsch <tfoertsch123(at)gmail(dot)com>
To: PostgreSQL mailing lists <pgsql-general(at)postgresql(dot)org>
Subject: plpgsql: ambiguous column reference in ON CONFLICT clause
Date: 2023-02-06 17:46:16
Message-ID: CAKkG4_=t5_0LCBYJueeb4yOKo5kMxvpnWUL3R48X9E+WTJf-dA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Is there a way to prevent this from happening? I know I can use the PK
constraint name or rename the OUT variable i. The question is can this be
resolved while keeping the arbiter inference and the variable name.

CREATE TABLE x.x (
i INT PRIMARY KEY
);

CREATE OR REPLACE FUNCTION x.ins(p_i INT, OUT i INT)
LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO x.x(i)
SELECT p_i
ON CONFLICT (i) DO NOTHING;
END
$$;

postgres=# select * from x.ins(1);
ERROR: column reference "i" is ambiguous
LINE 3: ON CONFLICT (i) DO NOTHING
^
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
QUERY: INSERT INTO x.x(i)
SELECT p_i
ON CONFLICT (i) DO NOTHING
CONTEXT: PL/pgSQL function x.ins(integer) line 3 at SQL statement

The conflicting variable is the OUT parameter of the function.

Normally, I'd suggest to fully qualify the name but the following or
similar is a syntax error:

INSERT INTO x.x(i) AS t
SELECT p_i
ON CONFLICT (t.i) DO NOTHING;

According to the documentation in
https://www.postgresql.org/docs/current/plpgsql-implementation.html:

> Query parameters will only be substituted in places where they are
syntactically permissible.

and

> Another way to understand this is that variable substitution can only
insert data values into an SQL command; it cannot dynamically change which
database objects are referenced by the command.

After reading this I am wondering if the current behavior is actually a bug.

Thanks,
Torsten

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2023-02-06 17:50:12 Re: plpgsql: ambiguous column reference in ON CONFLICT clause
Previous Message Thomas Kellerer 2023-02-06 17:43:52 Re: Get the sequence name corresponding to a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY column