Hi 2017-11-13 13:15 GMT+01:00 Pavel Golub <pa...@microolap.com>:
> Hello, Pavel. > > You wrote: > > PS> Hi, > > PS> I propose a new database object - a variable. The variable is > PS> persistent object, that holds unshared session based not > PS> transactional in memory value of any type. Like variables in any > PS> other languages. The persistence is required for possibility to do > PS> static checks, but can be limited to session - the variables can be > temporal. > > Great idea. > > PS> My proposal is related to session variables from Sybase, MSSQL or > PS> MySQL (based on prefix usage @ or @@), or package variables from > PS> Oracle (access is controlled by scope), or schema variables from > PS> DB2. Any design is coming from different sources, traditions and > PS> has some advantages or disadvantages. The base of my proposal is > PS> usage schema variables as session variables for stored procedures. > PS> It should to help to people who try to port complex projects to > PostgreSQL from other databases. > > PS> The Sybase (T-SQL) design is good for interactive work, but it > PS> is weak for usage in stored procedures - the static check is not > PS> possible. Is not possible to set some access rights on variables. > > PS> The ADA design (used on Oracle) based on scope is great, but our > PS> environment is not nested. And we should to support other PL than > PLpgSQL more strongly. > > PS> There is not too much other possibilities - the variable that > PS> should be accessed from different PL, different procedures (in > PS> time) should to live somewhere over PL, and there is the schema only. > > PS> The variable can be created by CREATE statement: > > PS> CREATE VARIABLE public.myvar AS integer; > PS> CREATE VARIABLE myschema.myvar AS mytype; > > PS> CREATE [TEMP] VARIABLE [IF NOT EXISTS] name AS type > PS> [ DEFAULT expression ] [[NOT] NULL] > PS> [ ON TRANSACTION END { RESET | DROP } ] > PS> [ { VOLATILE | STABLE } ]; > > > PS> It is dropped by command DROP VARIABLE [ IF EXISTS] varname. > > PS> The access rights is controlled by usual access rights - by > PS> commands GRANT/REVOKE. The possible rights are: READ, WRITE > > PS> The variables can be modified by SQL command SET (this is taken from > standard, and it natural) > > PS> SET varname = expression; > > I propose LET keyword for this to distinguish GUC from variables, e.g. > > LET varname = expression; > It is one possible variant. I plan to implement more variants and then choose one. Regards Pavel > > PS> Unfortunately we use the SET command for different purpose. But I > PS> am thinking so we can solve it with few tricks. The first is > PS> moving our GUC to pg_catalog schema. We can control the strictness > PS> of SET command. In one variant, we can detect custom GUC and allow > PS> it, in another we can disallow a custom GUC and allow only schema > PS> variables. A new command LET can be alternative. > > > > PS> The variables should be used in queries implicitly (without JOIN) > > > PS> SELECT varname; > > > PS> The SEARCH_PATH is used, when varname is located. The variables > PS> can be used everywhere where query parameters are allowed. > > > > PS> I hope so this proposal is good enough and simple. > > > PS> Comments, notes? > > > PS> regards > > > PS> Pavel > > > > > > > -- > With best wishes, > Pavel mailto:pa...@gf.microolap.com > >