because MySQL variables are not declared - and allows assign everywhere -
Ok. I do not do MySQL.
and MSSQL variables are not persistent.
Yep, but they might be?
In one session you can use lot of roles - some code can be used for securing interactive work, some can be for securing some API, sometimes you can secure a access to some sources. You can switch lot of roles by using security definer functions.
Hmmm. Switching role within a transaction. I never did need that... but that is a use case.
Any application with security definer functions - depends on different communities - it is used sometimes strongly.
If you need transactional content - then you should to use tables.
Why not.
Maybe variables just need be a syntactic convenience around that?
There is pretty similar relation between sequences and tables and variables and tables.
Yep. A sequence is a one row table, so a variable may be also a one row table as well, but with more flexibility about its type, and some nice syntactic sugar (like SERIAL which is syntactic sugar for CREATE SEQUENCE ...).
In first iteration the constraint can be implemented with domains - but there is not any break to implement constraints directly on variables.
Hmmm. If a variable is implemented as a one row table, then constraints are already available there, as well as grant & revoke, they can be any type including composite, nearly nothing to implement to get...
A "one row" table would be a CREATE + one INSERT, UPDATE allowed, further INSERT and DELETE are disallowed by construction. Then some syntactic sugar for variables (session => temporary table, persistent => standard table). Note sure about a "transaction variable", though... maybe an [unlogged] table automatically dropped on commit?
Probably we have different expectation from variables. I don't expect so variable can be changed by any rollback.
What is use case for transactional variables? I miss any experience - I wrote lot plpgsql lines and newer would it.
When I remove ACID, and allow only one value - then the implementation can be simple and fast - some next step can be support of expandable types. Sure - anybody can use temporary tables now and in future. But it is slow - more now, because we doesn't support global temporary tables. But ACID needs lot of CPU times, needs possible VACUUM, ...
No ACID variables are simple to implement, simple to directly accessible from any PL (although I am thinking about better support in 2nd phase for PLpgSQL).