Le 08/09/2021 à 13:41, Pavel Stehule a écrit :
Hi
so 28. 8. 2021 v 11:57 odesílatel Gilles Darold <gil...@darold.net
<mailto:gil...@darold.net>> napsal:
Hi,
Review resume:
This patch implements Schema Variables that are database objects
that can hold a single or composite value following the data type
used at variable declaration. Schema variables, like relations,
exist within a schema and their access is controlled via GRANT and
REVOKE commands. The schema variable can be created by the CREATE
VARIABLE command, altered using ALTER VARIABLE and removed using
DROP VARIABLE.
The value of a schema variable is local to the current session.
Retrieving a variable's value returns either a NULL or a default
value, unless its value is set to something else in the current
session with a LET command. The content of a variable is not
transactional. This is the same as in regular variables in PL
languages.
Schema variables are retrieved by the SELECT SQL command. Their
value is set with the LET SQL command. While schema variables
share properties with tables, their value cannot be updated with
an UPDATE command.
The patch apply with the patch command without problem and
compilation reports no warning or errors. Regression tests pass
successfully using make check or make installcheck
It also includes all documentation and regression tests.
Performances are near the set of plpgsql variable settings which
is impressive:
do $$
declare var1 int ; i int;
begin
for i in 1..1000000
loop
var1 := i;
end loop;
end;
$$;
DO
Time: 71,515 ms
CREATE VARIABLE var1 AS integer;
do $$
declare i int ;
begin
for i in 1..1000000
loop
let var1 = i;
end loop;
end;
$$;
DO
Time: 94,658 ms
There is just one thing that puzzles me.We can use :
CREATE VARIABLE var1 AS date NOT NULL;
postgres=# SELECT var1;
ERROR: null value is not allowed for NOT NULL schema variable
"var1"
which I understand and is the right behavior. But if we use:
CREATE IMMUTABLE VARIABLE var1 AS date NOT NULL;
postgres=# SELECT var1;
ERROR: null value is not allowed for NOT NULL schema variable
"var1"
DETAIL: The schema variable was not initialized yet.
postgres=# LET var1=current_date;
ERROR: schema variable "var1" is declared IMMUTABLE
It should probably be better to not allow NOT NULL when IMMUTABLE
is used because the variable can not be used at all. Also
probably IMMUTABLE without a DEFAULT value should also be
restricted as it makes no sens. If the user wants the variable to
be NULL he must use DEFAULT NULL. This is just a though, the above
error messages are explicit and the user can understand what wrong
declaration he have done.
I wrote a check that disables this case. Please, see the attached
patch. I agree, so this case is confusing, and it is better to disable it.
Great, I also think that this is better to not confuse the user.
postgres=# CREATE IMMUTABLE VARIABLE var1 AS date NOT NULL;
ERROR: IMMUTABLE NOT NULL variable requires default expression
Working as expected. I have moved the patch to "Ready for committers".
Thanks for this feature.
--
Gilles Darold
http://www.darold.net/
