schema variables

Last year I worked on few successful projects - with Alvaro Herrera I finished XMLTABLE. I materialized my ideas about better user interface and I wrote pspg pager. Now, I am working on implementation of session variables. There are lot of concepts: MSSQL, Oracle, DB2 has own based on history, with advantages and disadvantages. I am working on schema variables, where session variables are joined to schema like tables, views, ... This concept is pretty strong. We can limit access by rights to schema, to object self. We can share variables across complete PostgreSQL environment - psql, PL languages, SQL. Maybe in next versions, the schema variables can holds constraints and triggers. I have workable prototype - now only scalar types are supported, but I hope so support of composite types will be early.

postgres=# CREATE VARIABLE foo AS numeric;
CREATE VARIABLE
postgres=# LET foo = 100;
LET 
postgres=# SELECT foo;
┌─────┐
│ foo │
╞═════╡
│ 100 │
└─────┘
(1 row)

postgres=# DO $$
postgres$# BEGIN
postgres$#   RAISE NOTICE '%', foo;
postgres$# END;
postgres$# $$;
NOTICE:  100
DO
postgres=# GRANT SELECT ON foo TO public;
GRANT
postgres=# LET foo = (SELECT count(*) FROM pg_class);
LET 
postgres=# SELECT foo;
┌─────┐
│ foo │
╞═════╡
│ 344 │
└─────┘
(1 row)

postgres=# SELECT * FROM generate_series(1,1000) g(i) WHERE i = foo;
┌─────┐
│  i  │
╞═════╡
│ 344 │
└─────┘
(1 row)

postgres=# SELECT * FROM generate_series(1,1000) g(i) WHERE i = public.foo;
┌─────┐
│  i  │
╞═════╡
│ 344 │
└─────┘
(1 row)

The schema variables are persistent database object, but the content self is temporal. The analogy can be Oracle's global temporary tables.

The goals of this project:

• fast non transactional non persistent (session based) storage,
• possibility to control access to stored data with PostgreSQL GRANT/REVOKE commands - schema variable can be filled by security definer function, and anywhere in session can be read, but cannot be changed,
• possibility to share data between different PostgreSQL environments,
• possibility to have a analogy of package variables for PLpgSQL,
• is should not block a possibility to check PLpgSQL code by plpgsql_check.