Thread: [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
On Wed, Feb 22, 2017 at 12:02 AM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
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)
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$
BEGIN
INSERT INTO testt(a) SELECT testf.a ON CONFLICT (a) DO NOTHING;
END
$body$;
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