Postrgesql version
PostgreSQL 17.0 (Debian 17.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
Description:
When a RETURNS TABLE function in PL/pgSQL includes a column name that matches a column in a table being targeted for an ON CONFLICT clause, PostgreSQL throws an error stating that the column reference is ambiguous. This happens even though the context of ON CONFLICT unambiguously refers to table column names, not PL/pgSQL variables.
This behavior leads to unexpected errors in scenarios where the function's RETURNS TABLE includes columns with the same name as those in the target table. Explicit qualification of column names in ON CONFLICT (e.g., table.column) also fails to resolve the issue.
Error Message:
ERROR: column reference "column_name" is ambiguous
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
CONTEXT: PL/pgSQL function some_function() line X at SQL statement
Steps to Reproduce:
- Create a table with a unique constraint:
CREATE TABLE example_table ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), column_name text NOT NULL, value numeric NOT NULL, UNIQUE (column_name)
);
- Create a function that returns a table with a column name matching one in the table:
CREATE OR REPLACE FUNCTION example_function(p_column_name text, p_value numeric) RETURNS TABLE(column_name text, value numeric) LANGUAGE plpgsql
AS
$$
BEGININSERT INTO example_table (column_name, value) VALUES (p_column_name, p_value) ON CONFLICT (column_name) DO UPDATE SET value = example_table.value + EXCLUDED.value RETURNING column_name, value INTO column_name, value;
RETURN NEXT;
END;
$$;- Call the function:
SELECT * FROM example_function('test', 42);Expected Behavior:
The function should execute successfully, as ON CONFLICT (column_name) unambiguously refers to the example_table.column_name.
Actual Behavior:
The function throws the following error:
ERROR: column reference "column_name" is ambiguous
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
