> 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.
>> 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?
--
Fabien.