2016-12-23 16:27 GMT+01:00 Fabien COELHO <coe...@cri.ensmp.fr>: > > Hello Pavel, > > The session variables should be: >> > > I have often wished I had such a feature, psql client side :-variables are > just awful raw text things. > > A few comments, mostly about the design: > > 1. persistent objects with temporal unshared typed content. The life of >> content should be limited by session or by transaction. The content is >> initialized to default (when it is defined) or to NULL when variable is >> first accessed in variable' time scope (session, transaction). >> >> CREATE VARIABLE [schema.]variable type [DEFAULT default_value] >> [TRANSACTION|SESION SCOPE] >> > > I'm not sure of the order, and from a parser perspective it is nice to > announce the type before the value. >
I little bit dislike this style - in my proposal the session variables are very near to a sequences - and we have not any special symbols for sequences. Session secure variables are some different than in MSSQL or MySQL - so I would not to use same syntax. I really would to use pg_class as base for metadata of variables - conflicts are not possible. I can reuse safe GRANT/REVOKE mechanism .. With different syntax it all lost sense - and I'll to implement it again. > > Maybe a SQL-server like @-prefix would be nice, something like: > > CREATE VARIABLE @foo TEXT DEFAULT 'hello' SCOPE SESSION; > > DROP VARIABLE [schema.]variable >> > > In the long term, What would be the possible scopes? > > TRANSACTION, SESSION, PERSISTANT ? > > Would some scopes orthogonal (eg SHARED between sessions for a USER in a > DATABASE, SHARED at the cluster level?). > I have a plan to support TRANSACTION and SESSION scope. Persistent or shared scope needs much more complex rules, and some specialized extensions will be better. > How to deal with namespace issues? > > 2. accessed with respecting access rights: >> >> GRANT SELECT|UPDATE|ALL ON VARIABLE variable TO role >> REVOKE SELECT|UPDATE|ALL ON VARIABLE variable FROM role >> > > At least for transaction and session scopes it does not make sense that > they would be accessible outside the session/transaction, so grant/revoke > do not seem necessary? It is necessary - and I think so it is fundamental feature - any other features can be more or less replaced by extensions, but this one cannot or not simply - you have to protect content against some users - some cookies, ids have to be protected. It can be used well with RLS. Ada language has packages, package variables. I would not to introduce packages because are redundant to schemas, but I need some mechanism for content protecting. I would not to introduce packages, because than I will have problem with joining ADA packages with Perl, Python. Instead I introduce secure granted access. More - I don't need to solve lexical scope - and I can use a wide used mechanism. > > > 3. accessed/updated with special function "getvar", "setvar": >> >> FUNCTION getvar(regclass) RETURNS type >> FUNCTION setvar(regclass, type) RETURNS void >> > > From an aesthetical point of view, I do not like that much. > > If you use CREATE & DROP, then logically you should use ALTER: > > CREATE VARIABLE @name TEXT DEFAULT 'calvin'; > CREATE VARIABLE @name TEXT = 'calvin'; > ALTER VARIABLE @name SET VALUE TO 'hobbes'; > ALTER VARIABLE @name = 'hoobes'; > DROP VARIABLE @name; > > Maybe "SET" could be an option as well, but it is less logical: > > SET @name = 'susie'; > > But then "SET @..." would just be a shortcut for ALTER VARIABLE. > I would to use a SET statement too. But it is used for another target now. Using ALTER in this content looks strange to me. It is used for changing metadata not a value. Next step can be support of SQL statements With SQL support you can do SELECT varname; SELECT * FROM compositevarname; UPDATE varname SET value TO xxx; UPDATE compositevarname SET field TO xxx; > Also a nicer way to reference them would be great, like SQL server. > > SELECT * FROM SomeTable WHERE name = @name; > > A function may be called behind the scene, I'm just arguing about the > syntax here... > > Important question, what nice syntax to assign the result of a query to a > variable? Maybe it could be: > > SET @name = query-returning-one-row; -- hmmm > SET @name FROM query-returning-one-row; -- maybe better > > Or: > > ALTER VARIABLE @name WITH one-row-query; > > Special variables could allow to get the number of rows modified by the > last option, like in PL/pgSQL but at the SQL level? > > 4. non transactional - the metadata are transactional, but the content is >> not. >> > > Hmmm... Do you mean: > > CREATE VARIABLE foo INT DEFAULT 1 SCOPE SESSION; > BEGIN; > SET @foo = 2; > ROLLBACK; > > Then @foo is 2 despite the roolback? Yuk! > > This is similar to sequences. If you need transactional content - then you should to use tables. > I think that if the implementation is based on some system table for > storage, then you could get the transaction properties for free, and it > seems more logical to do so: > > CREATE TEMPORARY TABLE pg_session_variables(name TEXT PRIMARY KEY, value > TEXT, oidtype, ...); > > CREATE VARIABLE @foo INTEGER; -- INSERT INTO TABLE ... > > SELECT * FROM x WHERE name = @foo; > -- SELECT * FROM x WHERE name = (SELECT value::INT FROM > pg_session_variables WHERE name='foo') > > So maybe some simple syntactic rewriting would be enough? Or some SPI > function? > > > -- > Fabien. >