Re: Bug in plpgsql with ON CONFLICT

On Wed, Feb 22, 2017 at 12:02 AM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>
wrote:

> plpgsql doesn't know that it shouldn't try to resolve references inside of
> an ON CONFLICT phrase:
>
> ~(at)REMC02PRYM9G8WP/97243# \set VERBOSITY verbose
>> ~(at)REMC02PRYM9G8WP/97243# CREATE TEMP TABLE testt(a int primary key);
>> CREATE TABLE
>> ~(at)REMC02PRYM9G8WP/97243# CREATE OR REPLACE FUNCTION pg_temp.testf(a int)
>> RETURNS void LANGUAGE plpgsql AS $body$
>> BEGIN
>> INSERT INTO testt(a) SELECT testf.a ON CONFLICT (a) DO NOTHING;
>> END
>> $body$;
>> CREATE FUNCTION
>> ~(at)REMC02PRYM9G8WP/97243# select pg_temp.testf(1);
>> ERROR: 42702: column reference "a" is ambiguous
>> LINE 1: INSERT INTO testt(a) SELECT testf.a ON CONFLICT (a) DO NOTHI...
>> ^
>> DETAIL: It could refer to either a PL/pgSQL variable or a table column.
>> QUERY: INSERT INTO testt(a) SELECT testf.a ON CONFLICT (a) DO NOTHING
>> CONTEXT: PL/pgSQL function pg_temp_3.testf(integer) line 3 at SQL
>> statement
>> LOCATION: plpgsql_post_column_ref, pl_comp.c:1077
>> ~(at)REMC02PRYM9G8WP/97243# CREATE OR REPLACE FUNCTION pg_temp.testf(a int)
>> RETURNS void LANGUAGE plpgsql AS $body$
>> BEGIN
>> INSERT INTO testt(a) SELECT testf.a ON CONFLICT (testf.a) DO NOTHING;
>> END
>> $body$;
>> ERROR: 42601: syntax error at or near ")"
>> LINE 3: ... INTO testt(a) SELECT testf.a ON CONFLICT (testf.a) DO NOTHI...
>> ^
>> LOCATION: scanner_yyerror, scan.l:1087
>> ~(at)REMC02PRYM9G8WP/97243#
>>
>
> Not sure if it'd be easier to fix plpgsql or to broaden the general
> grammar to allow qualified references in ON CONFLICT.
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://BlueTreble.com
> 855-TREBLE2 (855-873-2532)
>
>
>
> This is not a bug. It's a naming conflict due to having a parameter and a
column with the same name (a).

You can use different name for the parameter or tell Postgres how to
resolve such conflicts:

CREATE OR REPLACE FUNCTION pg_temp.testf(a int)
RETURNS void LANGUAGE plpgsql AS
$body$
#variable_conflict use_column
BEGIN
INSERT INTO testt(a) SELECT testf.a ON CONFLICT (a) DO NOTHING;
END
$body$;

See the documentation: https://www.postgresql.org/
docs/current/static/plpgsql-implementation.html

Pantelis Theodosiou