Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1ezFPY-0001JI-Qd for pgsql-hackers@arkaria.postgresql.org; Fri, 23 Mar 2018 05:38:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ezFPX-0007RY-Lc for pgsql-hackers@arkaria.postgresql.org; Fri, 23 Mar 2018 05:38:43 +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.89) (envelope-from ) id 1ezFPX-0007RO-CZ for pgsql-hackers@lists.postgresql.org; Fri, 23 Mar 2018 05:38:43 +0000 Received: from mail-wm0-x241.google.com ([2a00:1450:400c:c09::241]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ezFPU-0005MB-3q for pgsql-hackers@postgresql.org; Fri, 23 Mar 2018 05:38:42 +0000 Received: by mail-wm0-x241.google.com with SMTP id x82so1413176wmg.1 for ; Thu, 22 Mar 2018 22:38:39 -0700 (PDT) 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=CAHBSVoA8otHpC9IM7sN759ck6AwwIbYoK+KkpZDTug=; b=TR+FLZ3LpDRorWrbR3mjvJD11Sop0TqGozqUV67skY6CTz8aQTA3TokkYDPFZmVb2S CJ+lmtbGvynoGVu3tuTntJgvUQouYPordcss9naxtiM4hNCxo608dssc8dX0R+e/Ermd aK1L5dm4RMSSMhJfbU9bTH4w3qo37B57HXajAmY0WYrzXcIsIH6oF6bfDz8O+FbnDc/u DDr9EWu/IR7G1HDUhBum7Wt1aaqPphhGSsdMZwPU187gXIEVR/2jLicL6LCAu6vkJB5Q hKfQ+HQR8v7FhqKGoU9s3v2QTDK5raMrEQfOVXJHO62cCIf2DKbUSG30uxOL0we5D8fA /NSA== 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=CAHBSVoA8otHpC9IM7sN759ck6AwwIbYoK+KkpZDTug=; b=FrJ6LY8TmNaS48oxOQd4AH2o4Jnw6x37eTEILZuIBlDnGj5bC5Yx9Ub8eHn+uEuRTs HGZoyGLpUHY6osny0kbXZ+NZ9XnliuSVuUEr+vBm4mwtuUod6Fnv263nyNhGknZamiBx ImnT8ym2cI2+9GHvgbFRhyZxX0n52DGq+P3nkum8LHEUG6pNmDrJJ273HX87iuxJuttB wkzQGEBqcOmVW07T8uK6Haqi7XSV/f5Q2O5KGJrk6l7MFyy1OWBG1qmLacJm5H+6vQy6 5LFQQrREFwn09ST4z/auvvJaqgV5NVX9KP0+iMLgmb8W1oJRfUwDqA5qVp5eWdvD/Drh XUFg== X-Gm-Message-State: AElRT7FBUJCRQRAPfaUCVhtOJbHdTbC8T7fNwV2R3oG5hBSWWQXxXtVC kVdG2Uu4ROh01zz1zwmLo2Ynxg+AQQbKGX05WM4= X-Google-Smtp-Source: AG47ELsxzrvym93EWUNlDLxyLvlpVZkZYitQ2mxlm5ZsuU+3uClL/Q2sWkJnVOW3h05x5RiNDkVoMk077cU6bjxTqCU= X-Received: by 10.28.183.68 with SMTP id h65mr5388705wmf.35.1521783519171; Thu, 22 Mar 2018 22:38:39 -0700 (PDT) MIME-Version: 1.0 Received: by 10.223.166.197 with HTTP; Thu, 22 Mar 2018 22:37:58 -0700 (PDT) In-Reply-To: References: <623395617.20171113141500@gf.microolap.com> From: Pavel Stehule Date: Fri, 23 Mar 2018 06:37:58 +0100 Message-ID: Subject: Re: [HACKERS] proposal: schema variables To: Pavel Luzanov Cc: "David G. Johnston" , Pavel Golub , PostgreSQL Hackers Content-Type: multipart/alternative; boundary="001a1148d68c15db2205680dd6aa" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --001a1148d68c15db2205680dd6aa Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: base64 MjAxOC0wMy0yMSA2OjI0IEdNVCswMTowMCBQYXZlbCBTdGVodWxlIDxwYXZlbC5zdGVodWxlQGdt YWlsLmNvbT46DQoNCj4NCj4NCj4gMjAxOC0wMy0yMCAxODozOCBHTVQrMDE6MDAgUGF2ZWwgU3Rl aHVsZSA8cGF2ZWwuc3RlaHVsZUBnbWFpbC5jb20+Og0KPg0KPj4gSGkNCj4+DQo+PiBJIGFtIHNl bmRpbmcgbmV3IHVwZGF0ZS4gVGhlIGNvZGUgaXMgbGVzcyB1Z2x5LCBhbmQgdGhlIGN1cnJlbnQN Cj4+IGZ1bmN0aW9uYWxpdHkgaXMgKy8tIGZpbmFsIGZvciBmaXJzdCBzdGFnZS4gSXQgc2hvdWxk IGJlIGdvb2QgZW5vdWdoIGZvcg0KPj4gcGxheWluZyBhbmQgdGVzdGluZyB0aGlzIGNvbmNlcHQu DQo+Pg0KPj4gV2hhdCBpcyBzdXBwb3J0ZWQ6DQo+Pg0KPj4gMS4gc2NhbGFyLCBjb21wb3NpdGUg YW5kIGFycmF5IHZhcmlhYmxlcw0KPj4gMi4gY29tcG9zaXRlIGNhbiBiZSBkZWZpbmVkIG9uIHBs YWNlIG9yIHNvbWUgY29tcG9zaXRlIHR5cGUgY2FuIGJlIHVzZWQNCj4+IDMuIHZhcmlhYmxlLCBv ciBhbnkgZmllbGQgb2YgdmFyaWFibGUsIGNhbiBoYXZlIGRlZmluZWQgZGVmYXVsdCB2YWx1ZQ0K Pj4gNC4gdmFyaWFibGUgaXMgZGF0YWJhc2Ugb2JqZWN0IC0gdGhlIGFjY2VzcyByaWdodHMgYXJl IHJlcXVpcmVkDQo+PiA1LiB0aGUgdmFsdWVzIGFyZSBzdG9yZWQgaW4gYmluYXJ5IGZvcm0gd2l0 aCBkZWZpbmVkIHR5cG1vZA0KPj4NCj4+IEFuIHVzYWdlIGlzIHZlcnkgc2ltcGxlOg0KPj4NCj4+ IHBvc3RncmVzPSMgY3JlYXRlIHZhcmlhYmxlIGZvbyBhcyBudW1lcmljIGRlZmF1bHQgMDsNCj4+ IENSRUFURSBWQVJJQUJMRQ0KPj4gcG9zdGdyZXM9IyBzZWxlY3QgZm9vOw0KPj4g4pSM4pSA4pSA 4pSA4pSA4pSA4pSQDQo+PiDilIIgZm9vIOKUgg0KPj4g4pWe4pWQ4pWQ4pWQ4pWQ4pWQ4pWhDQo+ PiDilIIgICAwIOKUgg0KPj4g4pSU4pSA4pSA4pSA4pSA4pSA4pSYDQo+PiAoMSByb3cpDQo+Pg0K Pj4gcG9zdGdyZXM9IyBsZXQgZm9vID0gcGkoKTsNCj4+IExFVA0KPj4gcG9zdGdyZXM9IyBzZWxl Y3QgZm9vOw0KPj4g4pSM4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA 4pSA4pSA4pSA4pSA4pSQDQo+PiDilIIgICAgICAgZm9vICAgICAgICDilIINCj4+IOKVnuKVkOKV kOKVkOKVkOKVkOKVkOKVkOKVkOKVkOKVkOKVkOKVkOKVkOKVkOKVkOKVkOKVkOKVkOKVoQ0KPj4g 4pSCIDMuMTQxNTkyNjUzNTg5Nzkg4pSCDQo+PiDilJTilIDilIDilIDilIDilIDilIDilIDilIDi lIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilJgNCj4+ICgxIHJvdykNCj4+DQo+PiBwb3N0 Z3Jlcz0jIGNyZWF0ZSB2YXJpYWJsZSBib28gYXMgKHggbnVtZXJpYyBkZWZhdWx0IDAsIHkgbnVt ZXJpYyBkZWZhdWx0DQo+PiAwKTsNCj4+IENSRUFURSBWQVJJQUJMRQ0KPj4gcG9zdGdyZXM9IyBs ZXQgYm9vLnggPSAxMDA7DQo+PiBMRVQNCj4+IHBvc3RncmVzPSMgc2VsZWN0IGJvbzsNCj4+IOKU jOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUkA0KPj4g4pSCICAgYm9vICAg4pSCDQo+PiDi lZ7ilZDilZDilZDilZDilZDilZDilZDilZDilZDilaENCj4+IOKUgiAoMTAwLDApIOKUgg0KPj4g 4pSU4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSYDQo+PiAoMSByb3cpDQo+Pg0KPj4gcG9z dGdyZXM9IyBzZWxlY3QgYm9vLng7DQo+PiDilIzilIDilIDilIDilIDilIDilJANCj4+IOKUgiAg eCAg4pSCDQo+PiDilZ7ilZDilZDilZDilZDilZDilaENCj4+IOKUgiAxMDAg4pSCDQo+PiDilJTi lIDilIDilIDilIDilIDilJgNCj4+ICgxIHJvdykNCj4+DQo+PiBQbGVhc2UgdHJ5IGl0Lg0KPj4N Cj4NCj4gc21hbGwgZml4IC0gc3VwcG9ydCBmb3IgU1FMIGZ1bmN0aW9ucw0KPg0KPg0KDQp0aGUg cGF0Y2ggaXMgaW4gY29tbWl0IGZlc3QgbGlzdCBodHRwczovL2NvbW1pdGZlc3QucG9zdGdyZXNx bC5vcmcvMTgvMTYwOC8NCg0KUmVnYXJkcw0KDQpQYXZlbA0KDQoNCj4NCj4+IFJlZ2FyZHMNCj4+ DQo+PiBQYXZlbA0KPj4NCj4NCj4NCg== --001a1148d68c15db2205680dd6aa Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


2018-03-21 6:24 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.c= om>:


2018-03-20 18:38 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:
H= i

I am sending new update. The code is less ugly, and the curr= ent functionality is +/- final for first stage. It should be good enough fo= r playing and testing this concept.

What is supported:
1. scalar, composite and array variables
2. composite can b= e defined on place or some composite type can be used
3. variable,= or any field of variable, can have defined default value
4. varia= ble is database object - the access rights are required
5. th= e values are stored in binary form with defined typmod

An usage is very simple:

postgres=3D# create variable foo as numeric default 0;
CREATE V= ARIABLE
postgres=3D# select foo;
=E2=94=8C=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=90
=E2=94=82 foo =E2=94=82
=E2=95=9E=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=A1
=E2=94=82=C2=A0=C2=A0 0= =E2=94=82
=E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =98
(1 row)

postgres=3D# let foo =3D pi();
LET
postgres=3D= # select foo;
=E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=90
=E2=94=82=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 foo=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =E2= =94=82
=E2=95=9E=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=A1
=E2=94=82 3.14159265358979 = =E2=94=82
=E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=98
(1 row)

postgres=3D# create variable bo= o as (x numeric default 0, y numeric default 0);
CREATE VARIABLE
post= gres=3D# let boo.x =3D 100;
LET
postgres=3D# select boo;
=E2=94= =8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=90
=E2=94=82=C2=A0=C2=A0 boo=C2=A0=C2=A0 =E2=94=82
= =E2=95=9E=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=A1
=E2=94=82 (100,0) =E2=94=82
=E2=94=94=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=98
(1 row)

postgres=3D# select boo.x;
=E2=94=8C=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=90
=E2=94=82=C2=A0 x=C2=A0= =E2=94=82
=E2=95=9E=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =A1
=E2=94=82 100 =E2=94=82
=E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=98
(1 row)

Please try= it.

small fix - support for SQL functions
=C2=A0

the patch is in commit fest list= https://commitfest.= postgresql.org/18/1608/

Regards

Pav= el
=C2=A0
=

Regards

Pavel


--001a1148d68c15db2205680dd6aa--