public inbox for pgsql-performance@postgresql.org
help / color / mirror / Atom feedFrom: Pavel Stehule <pavel.stehule@gmail.com>
To: Gilles Darold <gilles.darold@dalibo.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: proposal: schema variables
Date: Fri, 27 Oct 2017 16:09:36 +0200
Message-ID: <CAFj8pRCw5ok00vacRFDT8XpZQC5B=evKE+-TebEKM8-b+AzL8A@mail.gmail.com> (raw)
In-Reply-To: <cfca987e-8316-9f9d-6a85-f894e66b44c2@dalibo.com>
References: <CAFj8pRDY+m9OOxfO10R7J0PAkCCauM-TweaTrdsrsLGMb1VbEQ@mail.gmail.com>
<cfca987e-8316-9f9d-6a85-f894e66b44c2@dalibo.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
2017-10-27 15:38 GMT+02:00 Gilles Darold <gilles.darold@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
>
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pavel.stehule@gmail.com, gilles.darold@dalibo.com, pgsql-hackers@postgresql.org
Subject: Re: proposal: schema variables
In-Reply-To: <CAFj8pRCw5ok00vacRFDT8XpZQC5B=evKE+-TebEKM8-b+AzL8A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox