Ambiguous function arguments

Question

The example function:

CREATE OR REPLACE FUNCTION update_a_table(id int, name text)
RETURNS void AS $$
BEGIN
    UPDATE a_table 
    SET name = name
    WHERE id = id;
END;
$$ LANGUAGE plpgsql;

cause this error:

ERROR:  column reference "name" is ambiguous
LINE 2:   SET name = name
                     ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.

It is clear that I can correct this by changing the names of arguments. Is there any alternative solution?

Answer 1

Generally it is a bad practice in programming to use the same name for two different objects. You should not do it and the change of the arguments names is the best solution. However, Postgres leaves the door open (for the compatibility with older versions). You can set the configuration parameter:

set plpgsql.variable_conflict to use_variable;

Possible values of the parameter: error (default), use_variable or use_column.

It is also possible to set the parameter only for a given function:

CREATE OR REPLACE FUNCTION update_a_table(id int, name text)
RETURNS void AS $$
#variable_conflict use_variable
BEGIN
    UPDATE a_table 
    SET name = name
    WHERE id = id;
END;
$$ LANGUAGE plpgsql;

Alternatively, you can explicitly qualify ambiguous names, which is a better solution than the above one. The qualified names are of the shape <function_name>.<parameter_name>, for example update_a_table.id.

CREATE OR REPLACE FUNCTION update_a_table(id int, name text)
RETURNS void AS $$
BEGIN
    UPDATE a_table 
    SET name = update_a_table.name
    WHERE a_table.id = update_a_table.id;
END;
$$ LANGUAGE plpgsql;

Answer 2

This error is safer against to terribly difficult detectable bugs. The PLpgSQL good style requires

1. using qualified names everywhere in any embedded SQL - for access to function's parameters use a pattern function_name.parameter_name

2. previous versions of Postgres didn't this detection, and only one protection was using variables with special prefix. Usually is used '_' as prefix. The rule is simple - when your function contains embedded SQL, then all of the variable and parameter names should start with '_'. This is safer against name collision and it increase a readability, because you can see quickly what is a variable and what is a SQL identifier.

Answer 3

CREATE OR REPLACE FUNCTION update_a_table_1(int,text)
RETURNS void AS $$
BEGIN
    UPDATE a_table 
    SET name = $2
    WHERE id =$1;
END;
$$ LANGUAGE plpgsql;

You can create a function w/o specifying argument(s) name, in your case you have 2 args so you just need to access them by providing its index ($1 and $2) but IMO its not a good idea when you have many args to pass(for example a function with 10 or 10+ args it may confuse you)