session variables again

Hi

One of the significant issues of session variables (when a session variable
is not marked by special syntax) is possible collision between session
variable and column name.

Last week I got Jim Melton's book - "Understanding SQL's stored procedures"
where some basic ideas related to modules and variables are described. This
is based on the SQL/PSM standard.

https://www.biblio.com/9781558604612?srsltid=AfmBOooTlUWUleHcui1udD8tuqRUYSEMf1f80xYDqjXmaP1uYFPXs7GO

Until now, I didn't fully understand SQL/PSM modules and the relation
between modules and schema. What is important - Jim Melton writes - the
module is the schema object - is not another schema concept. It is a thin
wrapper of routines that can live inside a module or inside a schema. What
is important outside the module, the module cannot be referenced (PL/SQL
(DB2) breaks this rule). Inside modules there can be routines, module
(session) variables, module temporary tables. All routines inside the
module are owner defined (our security definer). SQL/PSM defines the
concept of PATH - this is a list of schemas that will be used for searching
unqualified names. The module by itself is implicitly first in this list
(it is similar to our SEARCH_PATH and pg_catalog). Any module can have a
redefined PATH. Any module has implicit PATH - that is owner SCHEMA. All
routines inside the module have this PATH.

Module variables are non-transactional with session scope (and they are not
shared between sessions). Variables are not visible outside the module, so
outside module, mentioned collision is not possible. Inside modules, the
collision is solved by priorities or by module reference - objects defined
inside modules shadows others. I think some implementation's details are
not described there - and the most significant implementation of SQL/PSM -
PL/SQL (db2) is in some details different.

CREATE MODULE my_schema.my_module;
CREATE FUNCTION my_fx() ...
ALTER MODULE my_schema.my_module ADD FUNCTION my_fx();
ALTER MODULE my_schema.my_module CREATE VARIABLE my_var int;

Inside the schema the function my_fx can be called:

my_fx(); -- shadows all for all routines from module
my_schema.my_fx();
my_catalog.my_schema.my_fx();
my_module.my_fx();

Outside the schema:
my_fx() .. when schema my_schema is in PATH
my_schema.my_fx();
my_catalog.my_schema.my_fx();

PL/SQL allows possibility to use module name in reference outside module:

so my_catalog.my_schema.my_module.my_fx() is valid syntax in DB2.

DB2 allows defining private routines that are not visible outside the
module.

I have to say that SQL/PSM modules are an interesting concept that can fix
some problems of PL/SQL. It can hold very important security features (like
fixed PATH or fact so module's object shadows any other). It can be a
strong security benefit, it can be a strong trap for developers. It
introduces inconsistency between schema routines and module routines. But
the security benefits cannot be without mentioned costs.

From my perspective (and today's knowledge) SQL/PSM modules are a useful
feature and can be nice to have. But it can significantly increase the
complexity of routines that implement searching column or function
identifiers in catalog.

The SQL/PSM concept is not in any serious conflict with my session variable
proposal. It is not surprising - a) it is similar to DB2 implementation, b)
one of the conceptual rules of SQL/PSM modules is - what is working inside
modules, should to work outside modules.

I proposed to solve the collision between column and variable identifier by
dedicated syntax - variable fence - SELECT VARIABLE(varname) ...

Comments, notes?

Regards

Pavel