Re: proposal: session server side variables

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.

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

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?

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

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!

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.