Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eEDve-00059b-00 for pgsql-hackers@arkaria.postgresql.org; Mon, 13 Nov 2017 12:33:30 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1eEDvd-00068a-IY for pgsql-hackers@arkaria.postgresql.org; Mon, 13 Nov 2017 12:33:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eEDtv-0006xM-5s for pgsql-hackers@postgresql.org; Mon, 13 Nov 2017 12:31:43 +0000 Received: from mail-wm0-x22f.google.com ([2a00:1450:400c:c09::22f]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1eEDts-0000Wo-Fi for pgsql-hackers@postgresql.org; Mon, 13 Nov 2017 12:31:42 +0000 Received: by mail-wm0-x22f.google.com with SMTP id z3so14859342wme.5 for ; Mon, 13 Nov 2017 04:31:39 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=oOEWNJySDSG2MG0axZueu60ls6qoIfFXjmO5A2N5gNw=; b=YxPq5rv4+Nr+69gy6H4281OPm+5K544MykqMHfWA1i0ikstJ72u8aDEOupJASKikEh d5rkgrqA5y+WT51HDCqF/adNEdFVesSHNs1AeC/WKOT+NS5/d33u+G4nxazEmkzl/7UG 8P3zMUQolbn8+AHkTm+5tGC0YbJPYmCto5OvlbYpIZmoFMjTgqhRWYnalb5cMGwHgSrQ 3c0So+GfQ0XJ/amsX2UvzQ6/Xd2qgA2A5UqeMk4I3+RskBQPWtKFPCa8WOi7ybTKG28A Jbsl2x/qjoshnMdqze7Bxx4FNdfd2fSSLwr5waoLMNRXI2LBUn8zomNQUuFzOgOe9Uup BrDw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=oOEWNJySDSG2MG0axZueu60ls6qoIfFXjmO5A2N5gNw=; b=XXReVrNQ63HFvESmL7XEub2Isn1/XInimLe+5mxOWtd6ziwggVp7mcCuudCfLdPbje gotgEryGMo/tAAfuU9J424+VwwUAyTDpOaN2GumKTladiZx3XqfXgxevKrdOYwOW+hbz dWGeSZQ/OgTBhH3wseXg7OMj9zPvuqJRc9nsWL14CxiU+5gK2RrKMjcmOpQA37I87y6V A0FPMPvc94yIUFfbSWORduVV8kdpjFKG8/5NWjSCrMd0KXY0ef9ivaQBBFR1BdO1y7t1 qLNvk1X/JsjCt70Akmw3tXH1frC0HcPnHR2JLNCAt+Tt8wFmKhOO33c8US+0wQ6Kq32s ewsQ== X-Gm-Message-State: AJaThX77ipiHvs2WePxPtEIl1gNuuyEWG79z1DSB7rytfXGUrrZfApth 7OlA0oVmSvrPGDZdxw3TkgEPYfbhPsRXtnF01sQ= X-Google-Smtp-Source: AGs4zMYlp7zfYXcyIR2ITa8xCfVI8UtHaCSXDncJkN9Q7bV7tAucY+0XCUfBGF51WFPj8sFv2mXGoBAJZrZRSmS9A8c= X-Received: by 10.28.111.76 with SMTP id k73mr6553215wmc.110.1510576298923; Mon, 13 Nov 2017 04:31:38 -0800 (PST) MIME-Version: 1.0 Received: by 10.223.146.7 with HTTP; Mon, 13 Nov 2017 04:30:58 -0800 (PST) In-Reply-To: <623395617.20171113141500@gf.microolap.com> References: <623395617.20171113141500@gf.microolap.com> From: Pavel Stehule Date: Mon, 13 Nov 2017 13:30:58 +0100 Message-ID: Subject: Re: proposal: schema variables To: Pavel Golub Cc: PostgreSQL Hackers Content-Type: multipart/alternative; boundary="001a1146a9dab41e80055ddc7365" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org --001a1146a9dab41e80055ddc7365 Content-Type: text/plain; charset="UTF-8" Hi 2017-11-13 13:15 GMT+01:00 Pavel Golub : > 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:pavel@gf.microolap.com > > --001a1146a9dab41e80055ddc7365 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

2017-11-13 13:15 GMT+01:00 Pavel Golub <pavel@microolap.com>:
Hello, Pavel.

You wrote:

PS> Hi,

PS> I propose a=C2=A0 new database object - a variable. The variable is<= br> 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 Post= greSQL from other databases.

PS> The Sybase=C2=A0 (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 PL= pgSQL 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> =C2=A0 [ DEFAULT expression ] [[NOT] NULL]
PS> =C2=A0 [ ON TRANSACTION END { RESET | DROP } ]
PS> =C2=A0 [ { VOLATILE | STABLE } ];


PS> It is dropped by command DROP VARIABLE=C2=A0 [ 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 =3D expression;

I propose LET keyword for this to distinguish GUC from variables, e.g.

LET varname =3D expression;

=C2=A0It is= one possible variant. I plan to implement more variants and then choose on= e.

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,
=C2=A0Pavel=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 mailto:pavel@gf.microolap.com


--001a1146a9dab41e80055ddc7365--