Re: BUG #14089: ON CONFLICT allows function variables in index expressions - Mailing list pgsql-bugs

From Alex Bolenok
Subject Re: BUG #14089: ON CONFLICT allows function variables in index expressions
Date
Msg-id CAMX8OqLNK8qXoZ9a8ucxcCo3eRTN-DEZ2uFn+2fOednhji=MEg@mail.gmail.com
Whole thread Raw
In response to Re: BUG #14089: ON CONFLICT allows function variables in index expressions  ("David G. Johnston" <david.g.johnston@gmail.com>)
Responses Re: BUG #14089: ON CONFLICT allows function variables in index expressions
List pgsql-bugs
This would have made sense if fn_test(1) would not fail (the last statement
in my script), but it does fail. Somehow the variable is constant enough
for the statement to parse, but is not constant enough for it to run.

It just does not make any sense.

fn_test is an SQL function, not PL/pgSQL, it should see the variable in
place of a constant on the parsing stage and not compile at all.

I realize there is an explanation to that (it's the way it's programmed,
after all), and that might be not a bug worthy enough to fix (however,
there are people actually affected by it, see here:
http://stackoverflow.com/questions/36656643/is-variable-conflict-use-variab=
le-not-working-with-on-conflict-clause-of-upsert
),
but it's clearly a bug in my opinion.

=D1=81=D0=B1, 16 =D0=B0=D0=BF=D1=80. 2016 =D0=B3. =D0=B2 5:22, David G. Joh=
nston <david.g.johnston@gmail.com>:

> On Friday, April 15, 2016, Alex Bolenok <quassnoi@gmail.com> wrote:
>
>> The function should not even compile, as the INSERT query before it does
>> not:
>>
>> test=3D# INSERT INTO test (value) SELECT * FROM (VALUES (1)) q (n) ON
>> CONFLICT
>> (value, (n)) DO NOTHING;
>> ERROR:  column "n" does not exist
>>
>> The parser should only allow the target table's column names and
>> constants in the index expression, as it does when creating the index. A
>> variable name is neither.
>>
>>
> The parsed insert never see the letter "n".  Once you comprehend that fac=
t
> your statement is true - because of the word "constant".  That constant i=
s
> why it compiles.
>
> David J.
>

pgsql-bugs by date:

Previous
From: rajeshwar.mukund@gmail.com
Date:
Subject: BUG #14097: unalbe to locate runuser in /usr/pgsql-9.4/bin/postgresql94-setup
Next
From: Javier Mogetta
Date:
Subject: Error instalación PostgreSQL 8.2 en Win10