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.
>