Re: LET clause

Hi

ne 3. 1. 2021 v 13:13 odesílatel Joel Jacobson <joel(at)compiler(dot)org> napsal:

> Hi hackers,
>
> I just learned about a feature called "LET clause".
>
> It's not part of the SQL standard, but it's supported by Oracle
> [1], Couchbase [2] and AsterixDB [3].
>

This is not SQL language - it uses EQL language

It looks like this is only available in one Oracle's product - Oracle®
EndecaServer. In this environment the PL/SQL is not available there, so
some procedural features are necessary, but I don't see a high benefit of
this feature in environments with procedural languages - PL/SQL or
PL/pgSQL.

https://en.wikipedia.org/wiki/Endeca

Regards

Pavel

>
> I searched the pgsql-hackers archives and couldn't find any matches on
> "LET clause",
> so I thought I should share this with you in some people didn't know about
> it like me.
>
> "LET clauses can be useful when a (complex) expression is used several
> times within a query, allowing it to be written once to make the query more
> concise." [3]
>
> In the mentioned other databases you can do this with the LET keyword,
> which "creates a new variable and initializes it with the result of the
> expression you supply".
>
> Without the LET clause, your complex queries would need to be divided into
> two separate queries:
>
> * One query to get a particular value (or set of values), and
> * One query to use the value (or values) from the first query.
>
> The example below computes the Easter month and day for a given year:
>
> Work-around using CROSS JOIN LATERAL:
>
> CREATE FUNCTION compute_easter_day_for_year(year integer)
> RETURNS date
> LANGUAGE sql
> AS $$
> SELECT make_date(year, easter_month, easter_day)
> FROM (VALUES (year % 19, year / 100)) AS step1(g,c)
> CROSS JOIN LATERAL (VALUES ((c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30))
> AS step2(h)
> CROSS JOIN LATERAL (VALUES (h - (h/28)*(1 - (h/28)*(29/(h + 1))*((21 -
> g)/11)))) AS step3(i)
> CROSS JOIN LATERAL (VALUES ((year + year/4 + i + 2 - c + c/4) % 7)) AS
> step4(j)
> CROSS JOIN LATERAL (VALUES (i - j)) AS step5(p)
> CROSS JOIN LATERAL (VALUES (3 + (p + 26)/30, 1 + (p + 27 + (p + 6)/40) %
> 31)) AS step6(easter_month, easter_day)
> $$;
>
> (Other possible work arounds: Use MATERIALIZED CTEs or sub-queries with
> OFFSET 0 to prevent sub-query flattening.)
>
> If we instead would have LET clauses in PostgreSQL, we could do:
>
> CREATE FUNCTION compute_easter_day_for_year(year integer)
> RETURNS date
> LANGUAGE sql
> AS $$
> SELECT make_date(year, easter_month, easter_day)
> LET
> 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
> $$;
>
> Without LET clauses, SQL isn't terribly well suited to execute
> fundamentally stepwise imperative algorithms like this one.
>
> The work-around is to either sacrifice performance and conciseness and use
> a hack (CROSS JOIN LATERAL or CTE),
> or, leave the SQL realm and use a PL like plpgsql to get good performance
> and conciseness.
>
> I have no opinion if this is something for PostgreSQL,
> since I have no idea on how complicated this would be to implement,
> which means I can't estimate if the increased complication of an
> implementation
> would outweigh the possible added convenience/performance/conciseness
> gains.
>
> I just wanted to share this in case this idea was unknown to some people
> here.
>
> [1]
> https://docs.oracle.com/cd/E93962_01/bigData.Doc/eql_onPrem/src/reql_statement_let.html
> [2]
> https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/let.html
> [3] https://asterixdb.apache.org/docs/0.9.3/sqlpp/manual.html#Let_clauses
>
> Kind regards,
>
> Joel
>
>