Explicitly specifying use of IN/OUT variable in PL/pgSQL functions
От | Joel Jacobson |
---|---|
Тема | Explicitly specifying use of IN/OUT variable in PL/pgSQL functions |
Дата | |
Msg-id | CAASwCXctMON3ffb+=Hghk9nxZh3-tTY5uBPZKq6gUdmo8+dsqw@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Explicitly specifying use of IN/OUT variable in
PL/pgSQL functions
Re: Explicitly specifying use of IN/OUT variable in PL/pgSQL functions |
Список | 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.
В списке pgsql-hackers по дате отправления: