Thread: Explicitly specifying use of IN/OUT variable in PL/pgSQL functions

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.

Hello

you can use function name as qualifier

create or replace function fx(paramname type, ...)
returns ...
begin SELECT INTO fx.paramname, ...

Regards

Pavel Stehule

2012/3/13 Joel Jacobson <joel@trustly.com>:
> 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.
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

Joel Jacobson <joel@trustly.com> writes:
> 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.

Can't you qualify them with the function name?
        regards, tom lane