Re: proposal: schema variables

2017-10-27 15:38 GMT+02:00 Gilles Darold <gilles(dot)darold(at)dalibo(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>