Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tCXIQ-005Huh-Az for pgsql-hackers@arkaria.postgresql.org; Sun, 17 Nov 2024 04:54:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tCXIN-00Gjrm-0w for pgsql-hackers@arkaria.postgresql.org; Sun, 17 Nov 2024 04:53:59 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tCXIM-00Gjrd-EG for pgsql-hackers@lists.postgresql.org; Sun, 17 Nov 2024 04:53:59 +0000 Received: from mail-yw1-x112b.google.com ([2607:f8b0:4864:20::112b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tCXIK-002Izv-9N for pgsql-hackers@lists.postgresql.org; Sun, 17 Nov 2024 04:53:57 +0000 Received: by mail-yw1-x112b.google.com with SMTP id 00721157ae682-6ea7c26e195so19217237b3.0 for ; Sat, 16 Nov 2024 20:53:56 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1731819235; x=1732424035; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=Hp9tEM5aPe0zXc1iodyV6xmQ77CUbJMw+bWLmITS0io=; b=a75jguaSbCPFhId3EEgGeoRr5Be5guuzy7rjLjabM/FQE0wTBdcfB/tsRz93yjcHVb JGZGsYTzj0Lt2MIYgSF0fgKLB0OtTRCVNfFXQwm3g8xxNPLS59cAzVq/oVrRu2kf0G4Z P9rk+mT7inwmZlePxaOZ5MR5F6A3osX3Z7pDG2Vn1h/J4OuzAHMChjKPsH5xTu84T19w gcYZfv2ycZTc6ifJsySMx9LSTXzX6BkeXzwYQ0cO4yDTPjaTZdbXbkgs9aY9udWQkm86 31EqQzI7QZPyiDwHvSVhOB3XYHNcwuPSBggAfVhOcIqeRiEJgFcnPpqW80Sg+zTa0EBZ 7JQA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1731819235; x=1732424035; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=Hp9tEM5aPe0zXc1iodyV6xmQ77CUbJMw+bWLmITS0io=; b=oszsLGKgnlWYMEq+HnH08+Wr8RdtH+V2NdD1SprxZXOz/IrpiExTEu2F/EJWx8mPyJ utSqdxeY4KViEDpsOht7QNh0Ny6xDtxcow9pDOXRFwsC8MshHlERoPY3u21aumHHho24 Wjwy+jlJMQgLEaIVBDYbLSHQcwhsa477AnQ03YgAkpa4BmVLhTafPqJONfqA80rZ0PlY HEBTw/CYQ2glwQ41WyrtQ7f5oVd3WX4Gcy0dOOnoLUAD267yLAhoV0yV5uxS4dKBt/GW dLnPGQ91rcptwGbpzDdTtz4pEfzxPsZJx+wtZmBUHNIQdJTAQ6eW0oq4tcgDULpSfF1W 6jeQ== X-Forwarded-Encrypted: i=1; AJvYcCU2Bm42ImyTTckfanqwx9iLi8a5qYIesIYgthoYibHyCxMo+XLsO2j/BMCr6Sipf6mXo22zI3o72AP7Pcum@lists.postgresql.org X-Gm-Message-State: AOJu0YybEOJGfdzlYLF1bdx/kn3GnOfMUBKKusMtqn+Z9C4I7jKPCSiR RLvCuYp0INu8m/bob8E9Xwx0X/BDwcX9AkxVmbAUHKpDhtegYexoE7+MgcYzPXkXdf8rdnvXTXQ WQuxInTlMj8rIJTKU8O/1sO8z2kI= X-Google-Smtp-Source: AGHT+IGoMKuIptRmZMNDrmmiHts1WULOnH1VKogsD06j5F8UkNDHFiY6D0z/GRLrxvr8kARWIp4kEyUdDch5pucmU5k= X-Received: by 2002:a05:690c:6806:b0:6e2:b263:104a with SMTP id 00721157ae682-6ee55b8f893mr80481357b3.23.1731819235512; Sat, 16 Nov 2024 20:53:55 -0800 (PST) MIME-Version: 1.0 References: <3chredgnjcmccym2kczawfih226b4ac6co7p6z4jeofevrcosi@mrsxkx2x2c65> <78f3c264-658a-4007-ab79-4cc913fba675@technowledgy.de> <0e35aece-a138-4ee1-9bb8-92a4a55ff149@technowledgy.de> In-Reply-To: From: Pavel Stehule Date: Sun, 17 Nov 2024 05:53:17 +0100 Message-ID: Subject: Re: proposal: schema variables To: Wolfgang Walther Cc: Dmitry Dolgov <9erthalion6@gmail.com>, Laurenz Albe , Erik Rijkers , Michael Paquier , Amit Kapila , DUVAL REMI , PostgreSQL Hackers Content-Type: multipart/alternative; boundary="000000000000591884062714962f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000591884062714962f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable so 16. 11. 2024 v 23:07 odes=C3=ADlatel Pavel Stehule napsal: > > > so 16. 11. 2024 v 18:13 odes=C3=ADlatel Wolfgang Walther < > walther@technowledgy.de> napsal: > >> Pavel Stehule: >> > (global (temp)) table can hold 0, 1 or more rows (and rows are always >> > composite of 0..n fields). The variable holds a value of some type. >> > Proposed session variables are like plpgsql variables (only with >> > different scope). In Postgres there is a difference between a scalar >> > variable and composite variable with one field. >> >> I can store composite values in table columns, too. A table column can >> either be scalar or composite in that sense. >> >> So, maybe rephrase: Single-row, single-column (global (temp)) table =3D >> variable. One "cell" of that table. >> > > the tables are tables and variables are variables. For tables you have > INSERT, UPDATE, DELETE commands, for variables you have a LET command. > > and scalar is not a single column composite. > example assignment to scalar versus single composite LET a =3D 10 LET a.a =3D 10 or LET a =3D ROW(10) Single column tables can be the result of some ALTERS - or sometimes you can use a table type. But for example, plpgsql, very strongly differs between scalar and composite variables. So introducing the "new" concept - single field composite is scalar introduces strong inconsistency there. Regards Pavel > > The session variables can in some particular use cases replace global tem= p > tables, but this is not the goal. I would like to see global temp tables = in > Postgres too. Maybe session variables prepare a field for this, because > some people better understand global temp objects. But again my proposal = is > not related to global temp tables. This is a different feature. > > >> >> For me, the major difference between a variable and a table is, that the >> table has 0...n rows and 0...m columns, while the variable has *exactly* >> one in both cases, not 0 either. >> >> I must put tables into FROM, why not those nice mini-tables called >> variables as well? Because they are potentially scalar, you say! >> >> But: I can already put functions returning scalar values into FROM: >> >> SELECT * FROM format('hello'); >> >> The function returns a plain string only. >> >> I don't know. This just "fits" for me. >> > > There are more issues - one - when you use some composite in FROM clause, > then you expect an unpacked result. But there are a lot of uses, when > unpackaging is not wanted. There is a syntax for this but it is really no= t > intuitive and not well readable. > > >> >> Or to put it differently: I don't really care whether I have to write >> "(SELECT variable FROM variable)" instead of just "variable". I don't >> want session variables for the syntax, I want session variables, because >> they are **so much better** than custom GUCs. >> > > session variables are better than GUC for the proposed purpose. But it > should look like variables. The software should respect standards or comm= on > typical usage when it is possible. If we introduce fully proprietary > design, then it will be hell for all people who know any other databases. > And I don't see a strong benefit from this syntax. It solves just one cas= e, > it doesn't solve other possible issues, and it introduces another possibl= e > risk. > > Regards > > Pavel > > >> Best, >> >> Wolfgang >> >> --000000000000591884062714962f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
so 16. 11. 2024 v=C2=A023:07 odes=C3= =ADlatel Pavel Stehule <pavel= .stehule@gmail.com> napsal:


so 16. 11. 2024 v= =C2=A018:13 odes=C3=ADlatel Wolfgang Walther <walther@technowledgy.de> napsal:<= br>
Pavel Stehule: > (global (temp)) table can hold 0, 1 or more rows (and rows are always =
> composite of 0..n fields). The variable holds a value of some type. > Proposed session variables are like plpgsql variables (only with
> different scope). In Postgres there is a difference between a scalar <= br> > variable and composite variable with one field.

I can store composite values in table columns, too. A table column can
either be scalar or composite in that sense.

So, maybe rephrase: Single-row, single-column (global (temp)) table =3D variable. One "cell" of that table.

the tables are tables and variables are variables. For tables you ha= ve INSERT, UPDATE, DELETE commands, for variables you have a LET command.

and scalar is not a single column composite.

example

assignment to scalar versus single composite

LET a=C2=A0 =3D 10=C2=A0
LET a.a =3D 10 or LET a =3D ROW(10)

Single column tables can be the result of some ALTERS= - or sometimes you can use a table type. But for example, plpgsql, very st= rongly differs between scalar and composite variables. So introducing the &= quot;new" concept - single field composite is scalar introduces strong= inconsistency there.

Regards

Pavel

=C2=A0

The session variables can in some particular use case= s replace global temp tables, but this is not the goal. I would like to see= global temp tables in Postgres too. Maybe session variables prepare a fiel= d for this, because some people better understand global temp objects. But = again my proposal is not related to global temp tables. This is a different= feature.
=C2=A0

For me, the major difference between a variable and a table is, that the table has 0...n rows and 0...m columns, while the variable has *exactly* one in both cases, not 0 either.

I must put tables into FROM, why not those nice mini-tables called
variables as well? Because they are potentially scalar, you say!

But: I can already put functions returning scalar values into FROM:

=C2=A0=C2=A0 SELECT * FROM format('hello');

The function returns a plain string only.

I don't know. This just "fits" for me.
<= br>
There are more issues - one - when you use some composite in = FROM clause, then you expect an unpacked result. But there are a lot of use= s, when unpackaging is not wanted. There is a syntax for this but it is rea= lly not intuitive and not well readable.
=C2=A0

Or to put it differently: I don't really care whether I have to write <= br> "(SELECT variable FROM variable)" instead of just "variable&= quot;. I don't
want session variables for the syntax, I want session variables, because they are **so much better** than custom GUCs.

session variables are better than GUC for the proposed purpose.=C2= =A0 But it should look like variables. The software should respect standard= s or common typical usage when it is possible. If we introduce fully propri= etary design, then it will be hell for all people who know any other databa= ses. And I don't see a strong benefit from this syntax. It solves just = one case, it doesn't solve other possible issues, and it introduces ano= ther possible risk.=C2=A0

Regards
<= br>
Pavel


Best,

Wolfgang

--000000000000591884062714962f--