pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Torsten Förtsch <tfoertsch123@gmail.com>
To: PostgreSQL mailing lists <pgsql-general@postgresql.org>
Subject: Re: plpgsql: ambiguous column reference in ON CONFLICT clause
Date: Mon, 6 Feb 2023 09:50:12 -0800
Message-ID: <c3291903-0d0a-797e-7f3e-beda8de6cfd8@aklaver.com> (raw)
In-Reply-To: <CAKkG4_=t5_0LCBYJueeb4yOKo5kMxvpnWUL3R48X9E+WTJf-dA@mail.gmail.com>
References: <CAKkG4_=t5_0LCBYJueeb4yOKo5kMxvpnWUL3R48X9E+WTJf-dA@mail.gmail.com>

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.klaver@aklaver.com






view thread (4+ messages)  latest in thread

Message-ID: <c3291903-0d0a-797e-7f3e-beda8de6cfd8@aklaver.com>
Permalink:  ../c3291903-0d0a-797e-7f3e-beda8de6cfd8@aklaver.com/
Also on:    postgresql.org/message-id/c3291903-0d0a-797e-7f3e-beda8de6cfd8@aklaver.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: adrian.klaver@aklaver.com, tfoertsch123@gmail.com
  Subject: Re: plpgsql: ambiguous column reference in ON CONFLICT clause
  In-Reply-To: <c3291903-0d0a-797e-7f3e-beda8de6cfd8@aklaver.com>
  References: <CAKkG4_=t5_0LCBYJueeb4yOKo5kMxvpnWUL3R48X9E+WTJf-dA@mail.gmail.com> <c3291903-0d0a-797e-7f3e-beda8de6cfd8@aklaver.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox