The following bug has been logged on the website:
Bug reference: 15960
Logged by: Anton Dutov
Email address: anton.dutov@gmail.com
PostgreSQL version: 11.3
Operating system: Linux
Description:
Using upsert in stored procedure ON CONFLICT(id, ...) trows "ERROR: column
reference "id" is ambiguous" where id used as column name and as variable,
but ON CONFLICT can't use variables
Reproduce
CREATE TABLE IF NOT EXISTS test_table (id INT PRIMARY KEY, note TEXT);
CREATE OR REPLACE FUNCTION test_table_add (
in_id INT
,in_note TEXT
,OUT id INT
) RETURNS INT LANGUAGE plpgsql VOLATILE SECURITY DEFINER AS $$
BEGIN
INSERT INTO test_table (id, note) VALUES (in_id, in_note)
ON CONFLICT (id) DO UPDATE SET note = in_note;
END;
$$;
SELECT test_table_add (1, 'ONE');