RE: proposal: schema variables

Hello Pavel.

That looks pretty good to me !

I’m adding Philippe Beaudoin who was also interested in this topic.

Recap : We were looking for a way to separate variable from constants in the “Schema Variables” proposition from Pavel.
Pavel was saying that there are some limitations regarding the keywords we can use, as the community don’t want to introduce too much new keywords in Postgres SQL (PL/pgSQL is a different list of keywords).
“CONSTANT” is not a keyword in SQL for Now (though it is one in PL/pgSQL).
Pavel’s syntax allow to use it as a keyword in the “WITH OPTIONS” clause that is already supported.
… I think it’s a good idea.

The list of keywords is defined in : postgresql\src\include\parser\kwlist.h

Pavel, I saw that in DB2, those variables are called “Global Variables”, is it something we can consider changing, or do you prefer to keep using the “Schema Variable” name ?

De : Pavel Stehule [mailto:pavel(dot)stehule(at)gmail(dot)com]
Envoyé : jeudi 27 février 2020 15:38
À : DUVAL REMI <REMI(dot)DUVAL(at)CHEOPS(dot)FR>
Cc : PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Objet : Re: proposal: schema variables

Hi

3) Any way to define CONSTANTs ?
We already talked a bit about this subject and also Gilles Darold introduces it in this mailing-list topic but I'd like to insist on it.
I think it would be nice to have a way to say that a variable should not be changed once defined.
Maybe it's hard to implement and can be implemented later, but I just want to know if this concern is open.

I played little bit with it and I didn't find any nice solution, but maybe I found the solution. I had ideas about some variants, but almost all time I had a problem with parser's shifts because all potential keywords are not reserved.

last variant, but maybe best is using keyword WITH

So the syntax can looks like

CREATE [ TEMP ] VARIABLE varname [ AS ] type [ NOT NULL ] [ DEFAULT expression ] [ WITH [ OPTIONS ] '(' ... ')' ] ]

What do you think about this syntax? It doesn't need any new keyword, and it easy to enhance it.

CREATE VARIABLE foo AS int DEFAULT 10 WITH OPTIONS ( CONSTANT);

?

Regards

Pavel