Thread: LET clause
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.
Kind regards,
Joel
Hi
ne 3. 1. 2021 v 13:13 odesílatel Joel Jacobson <joel@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.
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 dateLANGUAGE sqlAS $$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 dateLANGUAGE sqlAS $$SELECT make_date(year, easter_month, easter_day)LETg = 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 implementationwould outweigh the possible added convenience/performance/conciseness gains.I just wanted to share this in case this idea was unknown to some people here.Kind regards,Joel
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
On Sat, Jan 16, 2021, at 17:21, Vik Fearing wrote:
> 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?
Yes, I've tried, it results in the same problem due to flattening:
PREPARE calc_easter_day_for_year AS
SELECT make_date($1::integer, easter_month, easter_day)
FROM (VALUES ($1::integer % 19, $1::integer / 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 (($1::integer + $1::integer/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)
;
SET plan_cache_mode = 'force_generic_plan';
EXPLAIN (ANALYZE, VERBOSE) EXECUTE calc_easter_day_for_year(2021);
Result (cost=0.00..1.14 rows=1 width=4) (actual time=1.612..1.616 rows=1 loops=1)
Output: make_date($1, (3 + (((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) - (((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (1 - ((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (29 / ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) + 1))) * ((21 - ($1 % 19)) / 11))))) - (((((($1 + ($1 / 4)) + ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) - (((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (1 - ((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (29 / ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) + 1))) * ((21 - ($1 % 19)) / 11)))))) + 2) - ($1 / 100)) + (($1 / 100) / 4)) % 7)) + 26) / 30)), (1 + ((((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) - (((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (1 - ((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (29 / ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) + 1))) * ((21 - ($1 % 19)) / 11))))) - (((((($1 + ($1 / 4)) + ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) - (((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (1 - ((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (29 / ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) + 1))) * ((21 - ($1 % 19)) / 11)))))) + 2) - ($1 / 100)) + (($1 / 100) / 4)) % 7)) + 27) + (((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) - (((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (1 - ((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (29 / ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) + 1))) * ((21 - ($1 % 19)) / 11))))) - (((((($1 + ($1 / 4)) + ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) - (((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (1 - ((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (29 / ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) + 1))) * ((21 - ($1 % 19)) / 11)))))) + 2) - ($1 / 100)) + (($1 / 100) / 4)) % 7)) + 6) / 40)) % 31)))
Planning Time: 6.132 ms
Execution Time: 2.094 ms
(4 rows)