2017-10-27 15:38 GMT+02:00 Gilles Darold <gilles.dar...@dalibo.com>: > Le 26/10/2017 à 09:21, Pavel Stehule a écrit : > > 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 > > > > > > Great feature that will help for migration. How will you handle CONSTANT > declaration? With Oracle it is possible to declare a constant as follow: > > > varname CONSTANT INTEGER := 500; > > > for a variable that can't be changed. Do you plan to add a CONSTANT or > READONLY keyword or do you want use GRANT on the object to deal with > this case? >
Plpgsql declaration supports CONSTANT I forgot it. Thank you Pavel > > Regards > > -- > Gilles Darold > Consultant PostgreSQL > http://dalibo.com - http://dalibo.org > > > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >