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