Re: Schema variables - new implementation for Postgres 15 - Mailing list pgsql-hackers

ne 2. 6. 2024 v 23:31 odesílatel Peter Eisentraut <peter@eisentraut.org> napsal:
>
> On 25.05.24 12:50, Pavel Stehule wrote:
> > It looks odd - It is not intuitive, it introduces new inconsistency
> > inside Postgres, or with solutions in other databases. No other database
> > has a similar rule, so users coming from Oracle, Db2, or MSSQL, Firebird
> > will be confused. Users that use PL/pgSQL will be confused.
>
> Do you have a description of what those other systems do?  Maybe you
> posted it already earlier?
>

I checked today

1. MySQL

MySQL knows 3 types of variables

global - the access syntax is @@varname - they are used like our GUC and only buildin system variables are supported

SET @@autocommit = off;
SELECT @@autocommit;

user defined variables - the access syntax is @varname - the behaviour is similar to psql variables, but they are server side

SET @x = 100;
SELECT @x;

local variables - only inside PL

CREATE PROCEDURE p1()
DECLARE x int;
BEGIN
  SET x = 100;
  SELECT x;
END;

variables has higher priority than column (like old plpgsql)

2. MSSQL

global variables - the access syntax is @@varname, they are used like GUC and little bit more - some state informations are there like @@ERROR, @@ROWCOUNT or @@IDENTITY

local variables - the access syntax is @varname, and should be declared before usage by DECLARE command. The scope is limited to batch or procedure or function, where DECLARE command was executed.

DECLARE @TestVariable AS VARCHAR(100)
SET @TestVariable = 'Think Green'
GO
PRINT @TestVariable

This script fails, because PRINT is executed in another batch. So I think so MSSQL doesn't support session variables

There are similar mechanisms like our custom GUC and usage current_setting and set_config functions. Generally, in this area is MSSQL very primitive

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

3. DB2

The "user defined global variables" are similar to my proposal. The differences are different access rights "READ, WRITE" x "SELECT, UPDATE". Because PostgreSQL has SET command for GUC, I introduced LET command (DB2 uses SET)

Variables are visible in all sessions, but value is private per session. Variables are not transactional. The usage is wider than my proposal. Then can be changed by commands SET, SELECT INTO or they can be used like OUT parameters of procedures. The search path (or some like that) is used for variables too, but the variables has less priority than tables/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 are (I think) different kinds of variables - accessed by the function GETVARIABLE('name', 'default) - it looks very similar ro our GUC and `current_setting` function. These variables can be set by connection string, are of varchar type and 10 values are allowed. Built-in session variables (configuration) can be accessed by the function GETVARIABLE too.

SQL stored procedures supports declared local variables like PL/pgSQL

4. Firebird

Firebird has something like our custom GUC. But it allow nested routines - so some functionality of session variables can be emulated with local variable and nested routines (but outer variables can be used only in Firebird 5)

The variables are accessed by syntax :varname - like psql, but if I understand to diagrams, the char ':' is optional

5. SQL/PSM

Standard introduces a concept of modules that can be joined with schemas. The variables are like PLpgSQL, but only local - the only temp tables can be defined on module levels. These tables can be accessed only from routines assigned to modules. Modules are declarative versions of our extensions (if I understand well, I didn't find any implementation). It allows you to overwrite the search patch for routines assigned in the module. Variables are not transactional, the priority - variables/columns is not specified.

6. Oracle

Oracle PL/SQL allows the use of package variables. PL/SQL is +/- ADA language - and package variables are "global" variables. They are not directly visible from SQL, but Oracle allows reduced syntax for functions without arguments, so you need to 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 the higher priority has SQL, inside non SQL commands like CALL or some PL/SQL command, the higher priority has packages.

The Oracle allows both syntax for calling function with zero arguments so

SELECT my_package.get_a FROM DUAL;

or

SELECT my_package.get_a() FROM DUAL;

Then there is less risk reduction of collision. Package variables persist in session

Another possibility is using variables in SQL*Plus (looks like our psql variables, with possibility to define type on server side)

The variable should be declared by command VARIABLE and can be accessed by syntax :varname in session before usage (maybe this step is 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;

This is something between MSSQL and MYSQL session variables - but internally it is binding parameters - what I know, Postgres cannot set these parameters as result of some pg operation.

SQL*Plus is strange creature

Generally, the possible collision between variables and columns are solved by

a) special syntax - using prefix like @ or :

b) dedicated functions

c) variables has lower priority than columns

You can see, the RDBMS allows different types of session variables, different implementations. Usually one system allows more implementation of session variables. There is a possibility of emulation implementation between RDBMS, but security setting is possible only in Oracle or DB2.

Regards

Pavel