Re: Schema variables - new implementation for Postgres 15

ne 2. 6. 2024 v 23:31 odesílatel Peter Eisentraut <peter(at)eisentraut(dot)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