Re: LET clause - Mailing list pgsql-hackers

On 1/3/21 1:12 PM, Joel Jacobson wrote:
> 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
writtenonce 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
itwith 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 agree on the conciseness, but I'm wondering what performance problem
you think there is with the CROSS JOIN LATERAL VALUES technique.  Have
you tried running an EXPLAIN (ANALYZE, VERBOSE) on that?


> 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
> 


-- 
Vik Fearing