Re: proposal: schema variables - Mailing list pgsql-hackers

so 16. 11. 2024 v 18:13 odesílatel 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 =
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