The introduction of custom_variable_classes and #variable_conflict in
9.0 partly solves the problem with mixing IN/OUT variables with column
names.
In 8.4 and before, it defaulted to the IN/OUT variable, if it shared
the name of a column.
In 9.0 the behaviour was changed to raise an error if a variable
shared the same name as a column.
This was an important an great change, as it catches unintentional
potentially very dangerous mixups of IN/OUT variables and column
names.
But it's still not possible to use the same names in IN/OUT variables
and column names, which is somewhat a limitation, if not at least it
can be argued it's ugly.
In situations when it's natural and makes sense to mix IN/OUT
variables and columns names, it would be nice to being able to
explicitly specifying you are referring to the IN or OUT variable with
a specific name.
In lack of better ideas, I propose to prefix conflicting variable with
IN or OUT, to allow using them, even if there is a column with a
conflicting name.
Example:
CREATE OR REPLACE FUNCTION Get_UserID(OUT UserID integer, Username
text) RETURNS INTEGER AS $BODY$
#variable_conflict use_column
BEGIN
SELECT UserID INTO OUT.UserID FROM Users WHERE Username = IN.Username;
IF NOT FOUND THEN INSERT INTO Users (Username) VALUES (IN.Username) RETURNING UserID
INTO STRICT OUT.UserID;
END IF;
RETURN;
END;
$BODY$ LANGUAGE plpgsql VOLATILE;
While IN is a reserved word, OUT is not, so I guess that's a bit of a
problem with existing code.
Perhaps some other words or symbols can be used.