Hi ne 3. 1. 2021 v 13:13 odesílatel Joel Jacobson <j...@compiler.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 > >