[BUGS] Bug in plpgsql with ON CONFLICT

plpgsql doesn't know that it shouldn't try to resolve references inside 
of an ON CONFLICT phrase:

> ~@REMC02PRYM9G8WP/97243# \set VERBOSITY verbose
> ~@REMC02PRYM9G8WP/97243# CREATE TEMP TABLE testt(a int primary key);
> CREATE TABLE
> ~@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
> ~@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
> ~@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
> ~@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)


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs