From: | "Joel Jacobson" <joel(at)compiler(dot)org> |
---|---|
To: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | LET clause |
Date: | 2021-01-03 12:12:58 |
Message-ID: | 3e36717b-689b-473c-a40f-bb35dd56ea5b@www.fastmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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].
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
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2021-01-03 13:12:00 | Re: LET clause |
Previous Message | Michael Paquier | 2021-01-03 12:05:09 | Re: doc review for v14 |