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

From: Joel Jacobson <joel(at)trustly(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Explicitly specifying use of IN/OUT variable in PL/pgSQL functions
Date: 2012-03-13 14:05:25
Message-ID: CAASwCXctMON3ffb+=Hghk9nxZh3-tTY5uBPZKq6gUdmo8+dsqw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2012-03-13 14:10:25 Re: pg_upgrade and statistics
Previous Message Andres Freund 2012-03-13 14:04:34 Re: patch for parallel pg_dump