Hi,
I am returning back to implementation of schema variables. The schema variables can be used as an alternative to package variables (Oracle's PL/SQL or ADA). The schema variables can be used as fast and safe storage of session information for RLS too.
The previous implementation had not cleanly implemented execution of the LET statement. It was something between query and utility, and although it was working - it was out of Postgres concept (with different implementation of queries and utilities).
I totally rewrote the implementation of the LET statement. I prepared two variants:
First variant is based on the introduction of the new command type CMD_LET and new very small executor node SetVariable (this is a very very reduced analogy of ModifyTable node). The code is consistent and what is important - the LET statement can be prepared. The execution is relatively fast from PLpgSQL too. Without any special support the execution has the same speed like non simple queries. The statement reuses an execution plan, but simple execution is not supported.
Second variant is implemented like a classic utility command. There is not any surprise. It is shorter, simple, but the LET statement cannot be prepared (this is the limit of all utility statements). Without special support in PLpgSQL the execution is about 10x slower than the execution of the first variant. But there is a new possibility of using the main parser from PLpgSQL (implemented by Tom for new implementation of assign statement in pg 14), and then this support in plpgsql requires only a few lines). When the statement LET is explicitly supported by PLpgSQL, then execution is very fast (the speed is comparable with the speed of the assign statement) - it is about 10x faster than the first variant.
I tested code
do $$
declare x int ;
begin
for i in 1..1000000
loop
let ooo = i;
end loop;
end;
$$;
variant 1 .. 1500 ms
variant 2 with PLpgSQL support .. 140 ms
variant 2 without PLpgSQL support 9000 ms
The slower speed of the first variant from PLpgSQL can be fixed. But for this moment, the speed is good enough. This is the worst case, because in the first variant LET statement cannot use optimization for simple query evaluation (now).
Now I think so implementation is significantly cleaner, and I hope so it will be more acceptable for committers.
I am starting a new thread, because this is a new implementation, and because I am sending two alternative implementations of one functionality.
Comments, notes, objections?
Regards
Pavel