Re: proposal: schema variables

so 16. 11. 2024 v 18:13 odesílatel Wolfgang Walther <walther(at)technowledgy(dot)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 =
> 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
>
>