long time I working on this topic. Session server side variables are one
major missing feature in PLpgSQL. Now I hope, I can summarize requests for
implementation in Postgres:
1. Should be used in any PL (PLpgSQL, PLPython, PLPerl, ..)
2. Should not block a implementation of ANSI/SQL SQL modules - the modules
and PSM languages are big chapter and should be implemented together and
maybe from scratch - isn't easy to inject it to our environment pretty.
More the modules are partially redundant with schemas and with our
extensions. This is reason, why I don't take functionality described in
3. The usage should be simple, secure and not limited by only PL usage.
I found very good inspiration in PostgreSQL sequences. They can be used
anywhere, the access to sequences is secure, the sequence interface is
The session variables should be:
1. persistent objects with temporal unshared typed content. The life of
content should be limited by session or by transaction. The content is
initialized to default (when it is defined) or to NULL when variable is
first accessed in variable' time scope (session, transaction).
CREATE VARIABLE [schema.]variable type [DEFAULT default_value]
DROP VARIABLE [schema.]variable
2. accessed with respecting access rights:
GRANT SELECT|UPDATE|ALL ON VARIABLE variable TO role
REVOKE SELECT|UPDATE|ALL ON VARIABLE variable FROM role
The variable is joined with some schema - the access is filtered by schema
too - like any other schema object.
3. accessed/updated with special function "getvar", "setvar":
FUNCTION getvar(regclass) RETURNS type
FUNCTION setvar(regclass, type) RETURNS void
These functions are supported by PostgreSQL analyzer - a casting to correct
variable type is enforced there. These functions are volatile. Some stable
variants can exists too.
4. non transactional - the metadata are transactional, but the content is
This concept doesn't introduce any new visibility or accessibility methods.
The session variable is first class object like any others and special
rules are not necessary. The access should be controlled by access rights
This proposal doesn't propose Oracle's package variables and related
behave. When we have not a full ADA environment, then partial
implementation should be too complex with strange (foreign) behave in our
environment. But Oracle's package variables should be emulated over
proposed layer and this emulation should be really secure - no security by