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
