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
