Re: set_config() documentation clarification - Mailing list pgsql-hackers

On Tue, Jan 5, 2021, at 21:34, Pavel Stehule wrote:

> yes, it is supported. More the schema variables supports RESET to default on transaction end,

> and supports access rights for usage in security definer functions.

Nice.

> Maybe - I don't know what "Statement variables" :). Other databases do not have similarly named features.

I know, I made that name up just to make the connection,

the name used by other databases is "LET clause",

and in functional languages such as OCaml and Haskell,

this concept is called "let expressions".

> There are two concepts (these concepts can be mixed). Maybe - you can see there how non tabular objects can be 

> accessed in queries with.

> ...

Thank you for a detailed explanation, very useful.

>> Also, do you know if Schema variables are part of the SQL standard?

> ANSI SQL defines modules, and some variables can be declared in module scope. Modules are like our schemas with the

> possibility to define private objects. But I don't know any implementation of this part of the standard in some widely used

> database . It is like a mix of package concepts (Oracle) with schemas, because modules can hold private database objects

> like tables or temporary tables. So my proposed schema variables are not part of SQL standard, because related features

> depend on modules. Functionally it is similar +/-. Personally I don't like concepts of modules (or packages) too much. The

> schemas are a good replacement for 90% and the current system of qualified names and search path, that is same for

> tables and same for procedures, is very simple and good enough). So instead of introducing modules, I prefer enhanced

> schemas about some features like private objects. But it is in the category "nice to have" rather than a necessary feature.

This is encouraging to hear, then I will pray there might be hope for LET clauses I need,

even though not being part of the SQL standard.

In another attempt to sell the LET clause feature, imagine OCaml/Haskell *without* let expressions,

where users would be advised to write functions in a different language like C,

to do their complex computations reused at many places, and then return the result back to OCaml/Haskell.

That wouldn't be a very nice user-experience to the OCaml/Haskell user.

I really think a lot of real-life complex SQL code could be simplified a lot

and written much more clear and concise with LET clauses.

Since using "SET" as the command for Schema variables,

maybe using SET for LET clause would make the idea less controversial:

SET

  g = year % 19,

  c = year / 100,

  h = (c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30,

  i = h - (h/28)*(1 - (h/28)*(29/(h + 1))*((21 - g)/11)),

  j = year + year/4 + i + 2 - c + c/4) % 7,

  p = i - j,

  easter_month = 3 + (p + 26)/30,

  easter_day = 1 + (p + 27 + (p + 6)/40) % 31

SELECT make_date(year, easter_month, easter_day)

or maybe even WITH like this:

WITH

  year % 19 AS g ,

  year / 100 AS c,

  (c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30 AS h,

  h - (h/28)*(1 - (h/28)*(29/(h + 1))*((21 - g)/11)) AS i,

  year + year/4 + i + 2 - c + c/4) % 7 AS j,

  i - j AS p,

  3 + (p + 26)/30 AS easter_month,

  1 + (p + 27 + (p + 6)/40) % 31 AS easter_day

SELECT make_date(year, easter_month, easter_day)

I will study SQL code in the wild on Github written by other users to see how many %

that could benefit from this feature.

Maybe I'm wrong, but my gut feeling says this would be a really good thing,

and just like like Schema variables, I didn't really know I needed them before I saw them.

Best regards,

Joel