Alexi Theodore <alexitheodore@gmail.com> writes: > I'd like to report what I think is a bug. I've put together a script which highlights things pretty clearly. The short synopsis is that when using "#variable_conflict use_variable" in a function or procedure, the correct choice of variable vs column name is done everywhere (that I know of) except in the ON CONFLICT (<column name>) part of an INSERT statement. That one part only seems to not follow the conflict resolution pattern.
The names in ON CONFLICT are not values, and it would not make sense to substitute plpgsql variable values for them. A related example is that if you write INSERT INTO t (a,b) VALUES (1,2), none of t, a, or b are candidates to be replaced by plpgsql variables.
Then why is the 42P10 ERROR happening here? We don't get to see what the "ON CONFLICT specification" is in the error message though changing the call argument to 'test_column' doesn't remove the error (I truly expected that it would). Additionally, changing the resolution to "error" indeed causes an error which contradicts the claim that nothing in the presented INSERT command is ambiguous. And, if you specify "use_column" (which was the advice given a few weeks ago [1]) the query executes successfully. Omission of the directive results in an ambiguity error - so while I agree it doesn't make sense to substitute plpgsql variable values there the system is still trying (and failing to produce an expected result given its attempt and the correct column name is provided).
The interpretation of the ON CONFLICT clause in pl/pgsql does not abide by the expectations established for everything else. It boils down to the fact that the "ON CONFLICT (column_name)" syntax is not correctly identified as requiring an identifier. If it did then neither the #variable_conflict nor "((table_name.column_name))" solutions provided in [1] would be required - the simple and expected command would just work.
On a related question, for the syntax "ON CONFLICT ((index_expression))" is index_expression something that can be parameterized. I've been assuming so because I see nothing else about the ON CONFLICT clause that would need parameters (and thus end up interpreted by pl/pgsql as being a place where a variable makes sense). I am assuming we'd be able to describe the WHERE clause part of the clause separately.