On Fri, Apr 15, 2016 at 2:36 PM, <quassnoi@gmail.com> wrote:
> test=3D# INSERT INTO test (value) SELECT * FROM (VALUES (1)) q (n) ON CON=
FLICT
> (value, (n)) DO NOTHING;
> ERROR: column "n" does not exist
> =D0=A1=D0=A2=D0=A0=D0=9E=D0=9A=D0=90 1: ...CT * FROM (VALUES (1)) q (n) O=
N CONFLICT (value, (n)) DO
> NOT...
> =D0=9F=D0=9E=D0=94=D0=A1=D0=9A=D0=90=D0=97=D0=9A=D0=90: There is a colum=
n named "n" in table "*SELECT*", but it cannot
> be referenced from this part of the query.
>
> test=3D# DROP FUNCTION IF EXISTS fn_test(INT); CREATE FUNCTION fn_test(n =
INT)
> RETURNS VOID AS $ INSERT INTO test (value) VALUES (1) ON CONFLICT (value,
> (n)) DO NOTHING; $ LANGUAGE 'sql';
> DROP FUNCTION
> CREATE FUNCTION
>
> test=3D# SELECT * FROM fn_test(1);
> ERROR: there is no unique or exclusion constraint matching the ON CONFLI=
CT
> specification
> =D0=9A=D0=9E=D0=9D=D0=A2=D0=95=D0=9A=D0=A1=D0=A2: SQL function "fn_test"=
during startup
>
>
> I expected CREATE FUNCTION to fail with the same messages as the INSERT
> query before it, because it makes no sense to reference function variable=
s
> in index inference.
In the second example, the argument to the function is not represented
as a Var from an RTE during parse analysis. It is not a column in any
table, or even something that's column-like.
I don't think this is a bug. I guess it's a bit weird that the SQL
function argument is treated as something that inference considers as
a constant, but even if that is downright wrong it seems very unlikely
to cause any actual problem. This is a very contrived scenario. You
can make SQL function inlining do slightly odd things like this in
other places, too.
--=20
Peter Geoghegan