Implementation of declarative catalog session variables

From PostgreSQL wiki
Jump to navigationJump to search

Introduction

Session variables are database objects that can hold a value across multiple queries inside a session. The design of session variables varies widely, and many databases implement more than one type of session variable.

The SQL/PSM standard introduces modules and module-level variables. However, this part of the standard has not been implemented in any widely used product. As a result, each database system has developed its own proprietary design, syntax, and feature set.

Session variables are often part of the stored procedure environment, but there are exceptions --- for example, MySQL session variables or client-side session variables in tools like psql. In most systems, session variables do not participate in transactions: once set, their value persists until explicitly changed, even if the surrounding transaction is rolled back. Conceptually, they behave much like variables in general-purpose programming languages.

Because there is no common design, comparisons across systems are difficult. Each implementation has its own advantages and disadvantages, and in some cases a single system supports more than one kind of session variable.

Kinds of session variables:

  • client side (psql, pgadmin, sql plus, ...) - mostly similar to shell variables (based on string operations)
  • server side
    • declarative
      • created only for batch (T-SQL)
      • created as an database object (or part of database object) (Oracle, DB2)
    • created by usage (MySQL)
    • with priprietary syntax for identifiers (MySQL, T-SQL)
    • with ANSI SQL syntax for identifiers (Oracle, DB2)
    • Inside only PL (Oracle)
    • Inside db engine (MySQL, T-SQL, DB2)

Possible uses of session variables include:

  • Acting as global variables in PL code (for tracing, debugging, or quick hacks),
  • Storing configuration values for PL routines,
  • Keeping frequently used data handy in an interactive session,
  • Storing credentials for Row-Level Security (RLS) and other data-protection mechanisms,
  • Assisting with migrations from other systems (especially Oracle),
  • Enabling parameterization of anonymous code blocks (specific to PostgreSQL).
  • Session variables are writable even on read-only hot standby servers in PostgreSQL.

They behave somewhat like temporary tables, but there are important differences:

  • Variables hold single values, while tables hold sets of rows.
  • The syntax for using variables is shorter and more convenient for interactive work.
  • Access (read/write) to variables is generally faster.
  • The contents of session variables are typically non-transactional, whereas temporary tables are fully transactional.
  • A session variable stores only a single value or NULL (no MVCC, no VACUUM).
  • Temporary tables follow MVCC rules; repeated updates within a transaction generate many dead tuples, making updates more expensive.
  • Temporary tables are not cleaned up by autovacuum, and it is not possible to run VACUUM inside a function.
  • PostgreSQL does not support global temporary tables, so using a temporary table just to store a single value is inefficient and can lead to catalog bloat.

SQL/PSM

The SQL standard introduces the concept of modules, which can be associated with schemas (partially). Variables in modules behave like PL/pgSQL variables but are only local. The only objects allowed at the module level are temporary tables, which can be accessed only from routines assigned to that module. Modules can encapsulate private objects (functions, procedure, temp tables), that are not visible outside the module. Inside the module, a private objects shadows other objects. What I know the modules (and generally SQL/PSM) doesn't cover a deployment of code, so there is not similarity with PostgreSQL extensions. SQL/PSM Modules are modules like modules from modular languages like Modula2 or Oberon. There is some similarity with Postgres schemas (both are containers) with significant differences (there is not a concept of SEARCH_PATH (on PSM side) or there is not a concept of private or public objects (on Postgres schema side)).

SQL/PSM variables are not transactional (they are used exactly like in PL/pgSQL). The precedence between variables and columns is not specified.

Implementation Challenges

  • Possible collisions between session variables and other database objects.
  • Memory cleanup after dropping a session variable, especially when DDL operations can be reverted (Postgres has not support for global temp objects).
  • Memory invalidation: if a variable is dropped by one session, other sessions should not continue using it (Postgres has not support for global temp objects).

Proposed Implementations

It is clear that no single design is perfect for all use cases. MySQL’s approach is convenient and useful for interactive work, but it cannot be used for storing sensitive data and is considered unsafe. PostgreSQL GUCs are excellent for configuration, but they are not well suited for interactive use and are very limited when used as global variables in PL code.

Because of these trade-offs, no single design is sufficient. In practice, having multiple designs within a single system could support a broader range of use cases more effectively.

Design session variables as database global temporary typed objects with ANSI SQL syntax for identifiers

Author: Pavel Stehule <pavel.stehule@gmail.com>

I searched for a solution that could work well with the specific PostgreSQL environment:

  • PostgreSQL has more than one widely used stored procedure language: PL/pgSQL, PL/Python, or PL/Perl.
  • PL/pgSQL is a reduced version of PL/SQL, and PL/SQL is a modified ADA language. I am pretty happy with the current complexity of PL/pgSQL --- it is a domain-specific language that works well. It is very simple and easy to learn. PL/pgSQL has no packages or modules; instead PostgreSQL schemas are used. But schemas are database objects in their own right, independent of PL.
  • PostgreSQL already has a code container --- the schema. I don't want to introduce another container object (modules), as this would overlap heavily with schemas. Implementing modules for PL/pgSQL could be useful, but doing it properly would be quite complex. The main problem is the concept of public and private objects --- PostgreSQL does not have this. Don't forget: every PL/pgSQL expression is a SQL expression, so public/private visibility would first need to be implemented internally in PostgreSQL. That would be redundant (and possibly in conflict) with SEARCH_PATH.

So I wanted a design that supports multiple PL languages, does not add complexity to the relatively simple PL/pgSQL, and does not duplicate functionality already available.

I wrote a larger application in PL/pgSQL. To maintain it, I created plpgsql_lint (later renamed plpgsql_check). So I am always looking for solutions that do not block static code analysis. Static analysis requires persistent objects, which naturally leads to designing session variables as database objects (with their own system catalog). When session variables are database objects, ACLs can be applied naturally. Oracle package variables are well protected by package scope. PostgreSQL, however, has no schema scope --- schema locality is controlled by the SEARCH_PATH GUC, and introducing another mechanism would be messy. But with ACLs, variable contents can also be secured: 

 CREATE TEMP VARIABLE x AS int; LET x = 10; DO $$ BEGIN RAISE NOTICE '%', VARIABLE(x); END; $$; SELECT VARIABLE(x);

Collisions between column and variable identifiers

There is a risk of identifier collisions between variables and columns. With ANSI SQL syntax for session variable identifiers, the problem is how to distinguish variables from columns. This is a known issue in PL/pgSQL and PL/SQL and can be solved either by prioritization or by prohibiting collisions.

Prohibiting collisions has largely solved this problem in PL/pgSQL. Unfortunately, session variables are global objects, so collisions can occur in any query. A poorly named variable could break queries unexpectedly. Prioritization also has problems --- a variable or a column could be used silently when the other was intended. 

 CREATE TABLE tab(a int); CREATE VARIABLE a AS int; SELECT a FROM tab; -- with disallowed collisions, this query fails when a variable named "a" exists LET a = 1; DELETE FROM tab WHERE a = 1; -- with higher priority for variables, all rows could be deleted SELECT a FROM tab; -- with higher priority for columns, the variable is ignored

Even with collision prohibition, mistakes are still possible: 

 CREATE VARIABLE _id AS int; LET _id = 10; CREATE TABLE foo(id int); DELETE FROM foo WHERE _id = 10; -- just a typo, but all rows are deleted

These problems are not new. Developers in PL/pgSQL or PL/SQL know them, but the scope of risk is normally limited to stored procedures. After many discussions and designs, I introduced "variable fences" (the syntax VARIABLE(varname)). Inside any query, variables can be used only inside a variable fence. This completely solves collisions and reduces the chance of human error. Currently, fences are required everywhere in queries and expressions. In the future, they might be made optional inside PL/pgSQL, to reduce overhead when porting Oracle applications. This could be controlled by a new plpgsql.extra_checks setting.

A variable fence can collide with a user-defined function named `variable`. This is similar to keyword conflicts such as CUBE. It can be resolved by schema-qualifying or quoting: 

 SELECT public.variable(...) SELECT "variable"(...)

Why not use T-SQL (MSSQL, MySQL) syntax for session variables? There are several reasons – some objective, one subjective:

  • There is a collision with custom operators. Operators `@` and `@@` are already used, and `@@` is common:
 (2025-09-28 06:57:30) postgres=# create table tab(a int); CREATE TABLE (2025-09-28 06:57:42) postgres=# insert into tab values(10); INSERT 0 1 (2025-09-28 06:57:50) postgres=# select @a from tab; ┌──────────┐ │ ?column? │ ╞══════════╡ │ 10 │ └──────────┘ (1 row)

We could safely use only `:` as a prefix, but that would conflict with psql variables. Similarly, `$` would conflict with query parameters.

  • T-SQL variable names look strange in PostgreSQL (in queries and in PL/pgSQL). PostgreSQL uses only ANSI/SQL identifiers, and PL/pgSQL follows the same rules. PostgreSQL is consistent here, and I see little motivation to introduce a new, non-standard syntax (especially since it could cause compatibility issues).

Note: There was also a proposal to use table syntax for variables (similar to T-SQL in-memory table variables, but restricted to a single row). This is very effective for avoiding collisions, but I dislike it. It complicates simple expressions, adds inconsistency, and looks odd for scalar variables (though it could be useful for composite variables). This design does not solve every problem --- for example, variables can still be shadowed by tables because of SEARCH_PATH (a known issue). 

 CREATE VARIABLE var AS int; DO $$ BEGIN RAISE NOTICE '%', VARIABLE(var); -- variable is not a table END $$; CREATE VARIABLE var AS int; DO $$ BEGIN -- variable as a table (I don't like it) -- can we use DML? How many rows can it hold? -- is the value a row or not? RAISE NOTICE '%', (SELECT var FROM var); END $$

I am not against introducing in-memory tables --- or more correctly, global temporary tables. A well-designed implementation could be very useful. But this is a different feature and should be designed separately.

There are also performance considerations: PL/pgSQL can use simple expression evaluation when no tables are referenced. This optimization would need to change, which touches sensitive code. It would also be inconsistent.

I would like global temporary tables (which PostgreSQL currently lacks). Tables should behave like tables, and variables should behave like variables. The natural mental model for session variables comes from variables in PL languages. PostgreSQL internally supports *transition tables*, and I can imagine allowing these outside of triggers. That would be valid, but again, it is a different feature.

Implementing declared tables inside PL/pgSQL would be simpler from a high-level design perspective (interaction between SQL tables and PL/pgSQL is already well defined), but difficult from a low-level perspective (statistics, optimizer data, etc. --- the same issues as with global temporary tables, unless we add fake proxy local temp tables). 

 CREATE OR REPLACE FUNCTION fx() AS $$ -- not implemented yet, not part of this proposal DECLARE t TABLE(a int, b int); -- this table is invisible outside fx BEGIN INSERT INTO t VALUES(10,20); INSERT INTO t VALUES(30,40); RAISE NOTICE '%', (SELECT count(*) FROM t); END; $$ LANGUAGE plpgsql;

Some data languages define relational variables, but this concept has no overlap with the common understanding of session variables.

Inside PL/pgSQL it would be possible to configure the parser to allow the use of session variables without a variable fence (similar to ordinary PL/pgSQL variables) in expressions. This is not supported yet, but implementation should not be problematic. Outside PL, variable fences could in principle be optional when there is no risk of collisions (for example, in expressions without subselects). This has not been implemented yet.

Assign Command LET

The value of a session variable can be assigned either by a dedicated LET command or by the SELECT INTO construct. In most systems where a special command is needed, the keyword SET is used (MySQL, MSSQL, SQL/PSM, DB2). In PostgreSQL, SET could be extended to support a_expr at the parser level, but this would require partially rewriting the current implementation of the SET command. Technically, this should not be a problem.

The main issue is the potential for collisions between GUCs and session variables. GUCs are not declared in advance, are not associated with a schema (the prefix for a custom GUC can be any non-empty string), and access is not controlled by SEARCH_PATH. A variable fence could also solve this issue, but in this case the risk is always present, so fences could not be optional. Some languages use the keyword LET for assignments. Introducing LET in PostgreSQL provides a clean way to eliminate collisions between GUCs and session variables.

It would also be possible to allow assignment to session variables in PL code using the usual PL assignment syntax (not yet implemented). This would be beneficial when porting from PL/SQL (Oracle) and would provide a natural syntax when using session variables as PL global variables. There do not appear to be technical obstacles to implementing this. However, one important issue arises: in PL/pgSQL, only declared variables can be targets of assignment statements. If session variables were allowed here, either (a) this check could not be performed, or (b) PL functions would gain a strong dependency on session variables, similar to the dependency on types today. 

 (2025-10-02 07:42:01) postgres=# CREATE OR REPLACE FUNCTION fx() returns void as $$ begin declare var mydomain; begin var := 10; end $$ language plpgsql; ERROR: type "mydomain" does not exist LINE 4: declare var mydomain; ^

While such dependencies may not be a problem (the plan cache can be invalidated correctly), they raise new questions about temporary variables. To avoid these complications, the LET command was introduced, meaning the PL/pgSQL assignment mechanism does not need to be modified.

A major consideration is performance, both in general and when LET is used inside PL/pgSQL.

The LET command is a PostgreSQL utility command. Like other utility commands (e.g., CREATE TABLE AS SELECT), it can be prepared. Patches exist to implement PREPARE and EXECUTE for LET (these are not included in the reduced patch set). Even though the plan cache can be used, LET may still perform worse than a PL/pgSQL assignment for two reasons:

  • PL/pgSQL supports simple expression evaluation (also supported by LET in the enhanced patch set)
  • PL/pgSQL variables are stored in arrays and accessed via an integer offset, while session variables are stored in a hash table and accessed via a hash lookup, which is slower. This slowdown is visible only in worst-case benchmarks:
 DO $$ declare locvar int DEFAULT 0; begin for i in 1..1000000 loop locvar := locvar + 1; end loop; end $$ DO $$ begin for i in 1..100000 loop LET sesvar := sesvar + 1; end loop; end $$

In the reduced patch set, the LET implementation is significantly slower because simple expression evaluation is not used (although it is implemented in the extended patch set). Nevertheless, even with the slowdown, execution remains much faster than queries that touch real tables, so performance should not be an issue in practice.

Notes on Implementing Session Variables as Global Temporary Objects

The core of this proposal is to design session variables as global temporary objects. Unfortunately, PostgreSQL currently has no support for this type of object. Surprisingly non-trivial is the implementation of non-system objects that have a catalogue entry, but unlike other objects, keep the data purely in memory. The problem is in memory cleaning. There is no hook that could be used to catch the event when the object was 100% deleted. We can (and must - there is nothing else) catch the sinval message, however, it is often delivered as a false alarm.

Two main issues arise with this design:

  • the possibility of using invalid values (values stored in valid memory but no longer valid in the catalog),
  • and memory cleanup happening too early or too late.

Each session variable is identified by name (and possibly by an OID from pg_variable). A variable may have its own data type. Values stored in memory are kept in native binary format and remain unchanged until reassigned. Important note: an OID can be reused over time --- OIDs are unique only at a single moment, and there is no guarantee that an OID will not later be assigned to a different object. This creates a real possibility of the following issue:

  • session A: CREATE VARIABLE v AS t;
  • session B: LET v = t 'value';
  • session A: DROP VARIABLE v;
  • session B: no activity -- it got lot of sinval messages (signals), but no command is executed, and then is not possibility to handle sinval
  • session A: CREATE VARIABLE v AS nt; -- theoretically I can get same varid and typid like in first step, although t != nt
  • session B: SELECT v; -- crash

To prevent crashes, each stored value must be checked carefully before use. Checking only varid and typid is not sufficient. To ensure correctness, each variable can store its creation LSN (createlsn), which is unique throughout the lifetime of the database. Before using a value, the system checks that value.varid = catalog.varid and value.createlsn = catalog.createlsn. Type changes (ALTER VARIABLE ... ALTER TYPE) are not allowed. Dependencies ensure that the value type always matches the catalog type.

A second problem is deciding when to clean memory. Memory cannot be freed immediately after DROP VARIABLE because the command can be rolled back: 

 CREATE VARIABLE var AS int; BEGIN; LET var := 1; DROP VARIABLE var; ROLLBACK; SELECT VARIABLE(var); -- expected 1

or 

 BEGIN; CREATE VARIABLE var AS int; LET var := 1; ROLLBACK; -- var should be removed from memory

or 

 BEGIN; CREATE VARIABLE var AS int; SAVEPOINT s1; LET var := 1; DROP VARIABLE var; ROLLBACK TO s1; COMMIT; SELECT VARIABLE(var); -- expected 1

The simplest solution is to mark the variable as dropped and delay memory cleanup until the next transaction where variables are validated. This approach is simple and robust, although it means memory may only be freed after some delay. While this might look messy when monitoring memory usage, the catalog is transactional, and validation against the catalog ensures correctness.

Variabes in PostgreSQL (as of v18)

PostgreSQL provides relatively advanced client-side session variables in psql --- these variables use the `:` prefix. The implementation is pretty much straightforward: `psql`'s parser reads tokens from input and, when it encounters a token related to a variable, replaces it with the variable's value. The syntax supports literal and identifier escaping, and variables can be used as arguments of the `\bind` command.

`psql` variables are typeless client-side variables, available only inside `psql` or `pgbench`. 

 (2025-09-25 09:40:18) postgres=# \set myvar ahoj (2025-09-25 09:40:33) postgres=# \echo :myvar ahoj (2025-09-25 09:41:14) postgres=# select :'myvar'; ┌──────────┐ │ ?column? │ ╞══════════╡ │ ahoj │ └──────────┘ (1 row) (2025-09-25 09:41:24) postgres=# select $1 \bind :myvar \g ┌──────────┐ │ ?column? │ ╞══════════╡ │ ahoj │ └──────────┘ (1 row)

The `\set` command allows only simple string concatenation, but it also supports execution of shell commands. When a query is executed with the `\gset` command, its result can be stored in a `psql` variable. 

 (2025-09-25 09:41:40) postgres=# \set ctime `date` (2025-09-25 09:43:55) postgres=# \echo :ctime Thu Sep 25 09:43:55 CEST 2025

The `\set` command is fairly limited, making complex operations unreadable or non-portable.

This mechanism is good enough for writing tests, but it is not generally available (most users do not need it), and it is not accessible from the server side (e.g., from stored procedures). There is no simple way to access `psql` variables from an anonymous block --- a proxy custom GUC variable must be used: 

 (2025-09-25 09:58:38) postgres=# \echo :myvar ahoj (2025-09-25 09:58:43) postgres=# select set_config('myvars.myvar', :'myvar', false); ┌────────────┐ │ set_config │ ╞════════════╡ │ ahoj │ └────────────┘ (1 row) (2025-09-25 10:00:27) postgres=# do $$ begin raise notice '%', current_setting('myvars.myvar'); end $$; NOTICE: ahoj DO

PostgreSQL also has configuration variables (GUC - Grand Unified Configuration). These are primarily designed for PostgreSQL and extension configuration, but they can be used as session variables. GUC variables can be set with the `SET` command or the `set_config` function, and read with `SHOW` or `current_setting`.

GUC variables are meant for holding configuration parameters. When created through the internal API, they can be restricted to superusers, hidden from regular users, and assigned specific types (boolean, memory, number, interval, string, enum) with validation. This type system is separate from the SQL type system and is initialized before PostgreSQL’s SQL types.

Variables created from SQL must be "custom" variables, using a prefix in their name --- these are always strings. Such custom GUCs are often used as a workaround for missing server-side session variables in PostgreSQL, but they are typeless, unprotected, and unsafe.

A notable feature of PostgreSQL GUCs (built-in or custom) is that they can have different scopes: transaction, session, or function execution. They can also be assigned default values at specific events --- configuration loading, role login, database login, or function start. These features are useful for configuration, but problematic when GUCs are repurposed as session variables. 

 CREATE OR REPLACE FUNCTION fx() RETURNS void AS $$ BEGIN RAISE NOTICE '%', current_setting('my.x'); END $$ LANGUAGE plpgsql SET my.x = 20; CREATE FUNCTION (2025-09-27 06:10:37) postgres=# SET my.x = 0; SELECT fx(); SET NOTICE: 20 ┌────┐ │ fx │ ╞════╡ │ │ └────┘ (1 row) (2025-09-27 06:10:39) postgres=# SHOW my.x; ┌──────┐ │ my.x │ ╞══════╡ │ 0 │ └──────┘ (1 row)

GUC variables are transactional, and this behavior cannot be disabled. 

 (2025-09-27 06:17:31) postgres=# set my.x = 10; SET (2025-09-27 06:19:42) postgres=# begin; BEGIN (2025-09-27 06:19:46) postgres=# select set_config('my.x', '20', true); ┌────────────┐ │ set_config │ ╞════════════╡ │ 20 │ └────────────┘ (1 row) (2025-09-27 06:19:55) postgres=# show my.x; ┌──────┐ │ my.x │ ╞══════╡ │ 20 │ └──────┘ (1 row) (2025-09-27 06:20:01) postgres=# commit; COMMIT (2025-09-27 06:20:11) postgres=# show my.x; ┌──────┐ │ my.x │ ╞══════╡ │ 10 │ └──────┘ (1 row) (2025-09-27 06:20:13) postgres=# begin; BEGIN (2025-09-27 06:20:38) postgres=# select set_config('my.x', '20', false); ┌────────────┐ │ set_config │ ╞════════════╡ │ 20 │ └────────────┘ (1 row) (2025-09-27 06:20:45) postgres=# rollback; ROLLBACK (2025-09-27 06:20:52) postgres=# show my.x; ┌──────┐ │ my.x │ ╞══════╡ │ 10 │ └──────┘ (1 row) (2025-09-27 06:20:55) postgres=# begin; BEGIN (2025-09-27 06:22:36) postgres=# select set_config('my.x', '20', false); ┌────────────┐ │ set_config │ ╞════════════╡ │ 20 │ └────────────┘ (1 row) (2025-09-27 06:22:39) postgres=# commit; COMMIT (2025-09-27 06:22:42) postgres=# show my.x; ┌──────┐ │ my.x │ ╞══════╡ │ 20 │ └──────┘ (1 row)

As a result, there is no reliable way to store details such as error information in custom GUCs.

Session Variables in Other Systems

This section surveys how session variables are implemented in several widely used database systems. Each subsection includes code examples to illustrate syntax, behaviour, and scope.

MySQL

Session variables in MySQL have syntax similar to the better-known T-SQL variables (though other details are MySQL-specific). MySQL distinguishes two kinds of variables:

  • system variables - referenced using the `@@` prefix (or `@@scope.name`)
  • user-defined variables - referenced using the `@` prefix

System variables are modified with the `SET` statement. The `SET` statement accepts the modifiers `GLOBAL`, `SESSION`, or `PERSIST`. Some variables can only be set using the `GLOBAL` or `SESSION` modifier; when the variable name is specified with the `@@` prefix, it is not necessary to explicitly indicate the scope. System variables are defined by MySQL or by MySQL plugins. 

 SET GLOBAL some_config = 100; SET @@same_config = 100; SET SESSION sql_mode = 'TRADITIONAL'; SET @@sql_mode = 'TRADITIONAL'; SET @@SESSION.sql_mode = 'TRADITIONAL'; SET sql_mode = 'TRADITIONAL';

Persistent values are stored in `mysqld-auto.cnf` (in the server data directory).

Resetting system variables can be done by assigning DEFAULT or by copying from the GLOBAL namespace: 

 SET @@sql_mode = DEFAULT; SET @@sql_mode = @@GLOBAL.sql_mode

PostgreSQL equivalents: 

 SET GLOBAL ; -- there is not similar command in Postgres SET SESSION ; -- SET SET PERSIST ; -- ALTER SYSTEM; SELECT pg_reload_conf(); SELECT @@var; -- SELECT current_setting('var);

User-defined variables are created by assignment: 

 SET @var = expr [, @var2 = expr [ ... ]];

User-defined variables can only be of type integer, decimal, float, binary, non-binary string, or NULL. Casting between these types is implicit and hidden. User-defined variables cannot be used as table or column names.

An advantage of the T-SQL-style syntax (with special prefixes) is that it provides a simple solution for avoiding identifier collisions. MySQL variables are convenient, and the user experience can be good for people already familiar with T-SQL (MSSQL or Sybase). On the other hand, prefix-based syntax can be confusing for users coming from systems other than MSSQL. The type system is perhaps too simple and can be unsafe. In general, the performance of MySQL (or MariaDB) stored procedures is not good, and stored procedures are therefore not frequently used. There is no protection against typographical errors. Unassigned user variables can be used without error (they default to NULL), which makes static checking of stored procedures impossible in this area.

There is no way to restrict access to user-defined variables in MySQL. They cannot be protected against unintended usage. The only possible safeguard is a naming convention, since all user-defined variables share a single namespace.

Oracle

Oracle PL/SQL allows the use of package variables. PL/SQL is based on the Ada language, and package variables act as "global" variables. They are not directly visible from SQL, but Oracle provides a reduced syntax for functions without arguments, so you typically write a wrapper: 

 CREATE OR REPLACE PACKAGE my_package AS FUNCTION get_a RETURN NUMBER; END my_package; / CREATE OR REPLACE PACKAGE BODY my_package AS a NUMBER(20); FUNCTION get_a RETURN NUMBER IS BEGIN RETURN a; END get_a; END my_package; SELECT my_package.get_a FROM DUAL;

Inside SQL, SQL identifiers take precedence. Inside non-SQL commands (such as CALL or PL/SQL blocks), package identifiers take precedence.

Oracle allows both syntaxes for calling a function with zero arguments: 

 SELECT my_package.get_a FROM DUAL;

or 

 SELECT my_package.get_a() FROM DUAL;

This reduces the risk of naming collisions. Package variables persist for the duration of a session.

Another possibility is to use variables in SQL*Plus (similar to `psql` variables, but with the ability to define the type on the server side).

A variable is declared with the `VARIABLE` command and can be accessed in the session using the `:varname` syntax (a prior declaration step may be optional): 

 VARIABLE bv_variable_name VARCHAR2(30) BEGIN :bv_variable_name := 'Some Value'; END; SELECT column_name FROM table_name WHERE column_name = :bv_variable_name;

DB2

The "user-defined global variables" in DB2 are similar to the proposed PostgreSQL feature. The main difference is in the access rights: DB2 uses `READ` and `WRITE`, while PostgreSQL uses `SELECT` and `UPDATE`. Because PostgreSQL already uses the `SET` command for GUCs, the `LET` command was introduced in the proposal (DB2 uses `SET`).

Variables are visible in all sessions, but their values are private to each session. Variables are not transactional. Their usage is broader than in the proposal: they can be changed by `SET`, `SELECT INTO`, or used as OUT parameters of procedures. The search path (or a similar mechanism) applies to variables as well, but variables have lower priority than tables or columns. 

 CREATE VARIABLE myCounter INT DEFAULT 01; SELECT EMPNO, LASTNAME, CASE WHEN myCounter = 1 THEN SALARY ELSE NULL END FROM EMPLOYEE WHERE WORKDEPT = 'A00'; SET myCounter = 29;

There also appear to be other kinds of variables, accessed using the function `GETVARIABLE('name', 'default')`. These are very similar to PostgreSQL GUCs and the `current_setting` function. Such variables can be set via the connection string, are of type `VARCHAR`, and up to 10 values are allowed. Built-in session variables (configuration) can also be accessed using `GETVARIABLE`.

MSSQL (T-SQL)

MSSQL provides two types of variables:

  • Global variables — accessed with the `@@varname` syntax. These are similar to GUCs and provide state information such as `@@ERROR`, `@@ROWCOUNT`, or `@@IDENTITY`.
  • Local variables — accessed with the `@varname` syntax. These must be declared with the `DECLARE` command before use. Their scope is limited to the batch, procedure, or function where they are executed.
 DECLARE @TestVariable AS VARCHAR(100) SET @TestVariable = 'Think Green' GO PRINT @TestVariable

This script fails because `PRINT` is executed in a different batch. Therefore, it seems MSSQL does not truly support session variables.

There are also mechanisms similar to PostgreSQL's custom GUCs and the use of `current_setting` and `set_config` functions. In general, MSSQL is fairly primitive in this area. 

 EXEC sp_set_session_context 'user_id', 4; SELECT SESSION_CONTEXT(N'user_id');

ToDo

  • SECURITY LABEL support — enhance the `dummy_seclabel` module and the `sepgsql` extension. Update PostgreSQL documentation and ensure `SecLabelSupportsObjectType` includes session variables.
Retrieved from "https://wiki.postgresql.org/index.php?title=Implementation_of_declarative_catalog_session_variables&oldid=41901"