From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Torsten Förtsch <tfoertsch123(at)gmail(dot)com>, PostgreSQL mailing lists <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: plpgsql: ambiguous column reference in ON CONFLICT clause |
Date: | 2023-02-06 17:50:12 |
Message-ID: | c3291903-0d0a-797e-7f3e-beda8de6cfd8@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 2/6/23 09:46, Torsten Förtsch wrote:
> 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
> <https://www.postgresql.org/docs/current/plpgsql-implementation.html>:
>
> > Query parameters will only be substituted in places where they are
> syntactically permissible.
See:
https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-VAR-SUBST
for your choices.
>
> 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
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2023-02-06 18:02:21 | Re: plpgsql: ambiguous column reference in ON CONFLICT clause |
Previous Message | Torsten Förtsch | 2023-02-06 17:46:16 | plpgsql: ambiguous column reference in ON CONFLICT clause |