MIME-Version: 1.0
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Thu, 26 Oct 2017 09:21:24 +0200
Message-ID: 
 <CAFj8pRDY+m9OOxfO10R7J0PAkCCauM-TweaTrdsrsLGMb1VbEQ@mail.gmail.com>
Subject: proposal: schema variables
To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="94eb2c1cc5c86f1960055c6e07d9"
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

--94eb2c1cc5c86f1960055c6e07d9
Content-Type: text/plain; charset="UTF-8"

Hi,

I propose a  new database object - a variable. The variable is persistent
object, that holds unshared session based not transactional in memory value
of any type. Like variables in any other languages. The persistence is
required for possibility to do static checks, but can be limited to session
- the variables can be temporal.

My proposal is related to session variables from Sybase, MSSQL or MySQL
(based on prefix usage @ or @@), or package variables from Oracle (access
is controlled by scope), or schema variables from DB2. Any design is coming
from different sources, traditions and has some advantages or
disadvantages. The base of my proposal is usage schema variables as session
variables for stored procedures. It should to help to people who try to
port complex projects to PostgreSQL from other databases.

The Sybase  (T-SQL) design is good for interactive work, but it is weak for
usage in stored procedures - the static check is not possible. Is not
possible to set some access rights on variables.

The ADA design (used on Oracle) based on scope is great, but our
environment is not nested. And we should to support other PL than PLpgSQL
more strongly.

There is not too much other possibilities - the variable that should be
accessed from different PL, different procedures (in time) should to live
somewhere over PL, and there is the schema only.

The variable can be created by CREATE statement:

CREATE VARIABLE public.myvar AS integer;
CREATE VARIABLE myschema.myvar AS mytype;

CREATE [TEMP] VARIABLE [IF NOT EXISTS] name AS type
  [ DEFAULT expression ] [[NOT] NULL]
  [ ON TRANSACTION END { RESET | DROP } ]
  [ { VOLATILE | STABLE } ];

It is dropped by command DROP VARIABLE  [ IF EXISTS] varname.

The access rights is controlled by usual access rights - by commands
GRANT/REVOKE. The possible rights are: READ, WRITE

The variables can be modified by SQL command SET (this is taken from
standard, and it natural)

SET varname = expression;

Unfortunately we use the SET command for different purpose. But I am
thinking so we can solve it with few tricks. The first is moving our GUC to
pg_catalog schema. We can control the strictness of SET command. In one
variant, we can detect custom GUC and allow it, in another we can disallow
a custom GUC and allow only schema variables. A new command LET can be
alternative.

The variables should be used in queries implicitly (without JOIN)

SELECT varname;

The SEARCH_PATH is used, when varname is located. The variables can be used
everywhere where query parameters are allowed.

I hope so this proposal is good enough and simple.

Comments, notes?

regards

Pavel

--94eb2c1cc5c86f1960055c6e07d9
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div><div><div><div><div><div><div><div><div><div><div><di=
v><div><div><div>Hi,<br><br></div>I propose a=C2=A0 new database object - a=
 variable. The variable is persistent object, that holds unshared session b=
ased not transactional in memory value of any type. Like variables in any o=
ther languages. The persistence is required for possibility to do static ch=
ecks, but can be limited to session - the variables can be temporal. <br><b=
r></div>My proposal is related to session variables from Sybase, MSSQL or M=
ySQL (based on prefix usage @ or @@), or package variables from Oracle (acc=
ess is controlled by scope), or schema variables from DB2. Any design is co=
ming from different sources, traditions and has some advantages or disadvan=
tages. The base of my proposal is usage schema variables as session variabl=
es for stored procedures. It should to help to people who try to port compl=
ex projects to PostgreSQL from other databases.<br><br></div>The Sybase=C2=
=A0 (T-SQL) design is good for interactive work, but it is weak for usage i=
n stored procedures - the static check is not possible. Is not possible to =
set some access rights on variables.<br><br></div>The ADA design (used on O=
racle) based on scope is great, but our environment is not nested. And we s=
hould to support other PL than PLpgSQL more strongly. <br><br></div>There i=
s not too much other possibilities - the variable that should be accessed f=
rom different PL, different procedures (in time) should to live somewhere o=
ver PL, and there is the schema only. <br><br></div>The variable can be cre=
ated by CREATE statement:<br><br></div>CREATE VARIABLE public.myvar AS inte=
ger;<br></div>CREATE VARIABLE myschema.myvar AS mytype;<br><br></div>CREATE=
 [TEMP] VARIABLE [IF NOT EXISTS] name AS type <br></div>=C2=A0 [ DEFAULT ex=
pression ] [[NOT] NULL]</div><div>=C2=A0 [ ON TRANSACTION END { RESET | DRO=
P } ]</div><div>=C2=A0 [ { VOLATILE | STABLE } ];<br></div><div><br></div>I=
t is dropped by command DROP VARIABLE=C2=A0 [ IF EXISTS] varname.<br><br></=
div>The access rights is controlled by usual access rights - by commands GR=
ANT/REVOKE. The possible rights are: READ, WRITE<br><br></div>The variables=
 can be modified by SQL command SET (this is taken from standard, and it na=
tural)<br><br></div>SET varname =3D expression;<br><br></div>Unfortunately =
we use the SET command for different purpose. But I am thinking so we can s=
olve it with few tricks. The first is moving our GUC to pg_catalog schema. =
We can control the strictness of SET command. In one variant, we can detect=
 custom GUC and allow it, in another we can disallow a custom GUC and allow=
 only schema variables. A new command LET can be alternative.<br><div><br><=
/div><div>The variables should be used in queries implicitly (without JOIN)=
</div><div><br></div><div>SELECT varname;</div><div><br></div><div>The SEAR=
CH_PATH is used, when varname is located. The variables can be used everywh=
ere where query parameters are allowed. <br></div><div><br><div><div>I hope=
 so this proposal is good enough and simple.</div><div><br></div><div>Comme=
nts, notes?</div><div><br></div><div>regards</div><div><br></div><div>Pavel=
<br></div><div><div><div><div><div><div><div><br><br></div></div></div></di=
v></div></div></div></div></div></div>

--94eb2c1cc5c86f1960055c6e07d9--