Re: [HACKERS] proposal: schema variables
От | Pavel Stehule |
---|---|
Тема | Re: [HACKERS] proposal: schema variables |
Дата | |
Msg-id | CAFj8pRCw5ok00vacRFDT8XpZQC5B=evKE+-TebEKM8-b+AzL8A@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: [HACKERS] proposal: schema variables (Gilles Darold <gilles.darold@dalibo.com>) |
Список | pgsql-hackers |
2017-10-27 15:38 GMT+02:00 Gilles Darold <gilles.darold@dalibo.com>:
Great feature that will help for migration. How will you handle CONSTANTLe 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
>
>
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
В списке pgsql-hackers по дате отправления: