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 1tCQxn-001wMs-6M for pgsql-hackers@arkaria.postgresql.org; Sat, 16 Nov 2024 22:08:18 +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 1tCQxi-00FqdU-O5 for pgsql-hackers@arkaria.postgresql.org; Sat, 16 Nov 2024 22:08:15 +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 1tCQxi-00FqdM-3r for pgsql-hackers@lists.postgresql.org; Sat, 16 Nov 2024 22:08:14 +0000 Received: from mail-yw1-x112a.google.com ([2607:f8b0:4864:20::112a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tCQxc-002GLY-07 for pgsql-hackers@lists.postgresql.org; Sat, 16 Nov 2024 22:08:13 +0000 Received: by mail-yw1-x112a.google.com with SMTP id 00721157ae682-6ee7a48377cso3720767b3.3 for ; Sat, 16 Nov 2024 14:08:07 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1731794887; x=1732399687; 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=B9RRcsVZ7TUZtNrbsqLDOm2FZQv249CiY+EK1+f6EsU=; b=fOHtgfplcBrIilbVUDgTw8sUPmXyxdc7VpiG/ndh3vS5PD8Vc+sgrd29zzwp1xNESv ip1TCHxskzpDNnuyPn28/ly4y/PiW1ZxLwiA/0RW5QOqG52hr55otDUilRAzb2LD/jzO s5m85lBhj90OpwQ/rSoHtd6eybbGcTTiA6EYLzp5wUU+1nUJ3FhkfyaGD7B2huv4fs9e X9JiNZzB1acgBI5YiH35L39bzlF9R5MI1kWEiuCMMW27WOu33tVJoqMZiepU/EAyJtxw mViTlMMc/RIpMEyySTcEaFk7TsY+v26KIkgC0h30dYXqfHSrUhoBxlzXvvBOi8TXUgoz lysw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1731794887; x=1732399687; 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=B9RRcsVZ7TUZtNrbsqLDOm2FZQv249CiY+EK1+f6EsU=; b=sm9nWQdjVp5AQEiaXR0M7q9+Olfe44VK69v7QnhBRje80jpXITlMXCk1EcaDlkHqcl u9fo2YYvMFwAYS66EahVa7ec8ITwVnPkhdWrvCRU1q02xliSlSgtVOGMsUEyzmM8qvj6 ymcdmPKb4RBPOpzcP9BhHtfnB4PpnDTzGVCmL0X5fRjlAvOzT7JHOxSlBlv+SLBHXwRm 1ieYEP6X503biYWezEJmN4g226Qz55TDj0ANPYrFmpfNOQrICTGbNTb0KKekxKncVJTo u5wldrQSkwwxX4nSjPPzeAEBFuPrfYE4pY2NllGJ8CNG52MfuxyQqC40b9CT7GrPzBdd Tgsw== X-Forwarded-Encrypted: i=1; AJvYcCXMCCuvR0HG2fUQYq8S3PWO4ADhRPhIC94N1XrKLaIFprcE1OcthhnX8qHcZghvt+/YJx7TPV+sh8rsnkTz@lists.postgresql.org X-Gm-Message-State: AOJu0YyFVRThO4TT/X43eolZZ3ZozduLw+zW3RnQgr1LfrxKMbSakcQs d4+Fxe710ux53WRZs0NFDjJCv2wKwSObTyd6vs5Nkhkst/WziMZyq/0pIi/IledA3W5ARmp3lWD fwdZYdXqHVNFdED96qHIJKlhELI0= X-Google-Smtp-Source: AGHT+IGooM0tGdOqvrpWtvZAv3ctoKYpMpOyM69icrNFJxR7dwrkzncuQkd++NxrRQAXbYCpU8Er7yvRvPOOND3riqE= X-Received: by 2002:a05:690c:6b82:b0:6dd:c474:9cd8 with SMTP id 00721157ae682-6ee55b7ef77mr69581877b3.18.1731794887052; Sat, 16 Nov 2024 14:08:07 -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: <0e35aece-a138-4ee1-9bb8-92a4a55ff149@technowledgy.de> From: Pavel Stehule Date: Sat, 16 Nov 2024 23:07:29 +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="00000000000011142c06270eeb77" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000011142c06270eeb77 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable so 16. 11. 2024 v 18:13 odes=C3=ADlatel Wolfgang Walther 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. The session variables can in some particular use cases 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 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 not 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 common 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 case, it doesn't solve other possible issues, and it introduces another possible risk. Regards Pavel > Best, > > Wolfgang > > --00000000000011142c06270eeb77 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
so 16. 11. 2024 v=C2=A018:13 odes=C3= =ADlatel Wolfgang Walther <wa= lther@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 <= 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.

The session variables can in some particular use cas= es replace global temp tables, but this is not the goal. I would like to se= e global temp tables in Postgres too. Maybe session variables prepare a fie= ld for this, because some people better understand global temp objects. But= again my proposal is not related to global temp tables. This is a differen= t 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

--00000000000011142c06270eeb77--