Re: proposal: schema variables

so 16. 11. 2024 v 23:07 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

>
>
> 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.
>

example

assignment to scalar versus single composite

LET a  = 10
LET a.a = 10 or LET a = 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 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
>>
>>