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

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(at)trustly(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers