LATERAL allows a subquery or function to refer to the records that it's being joined to when computing results.
WITH RECURSIVE allows a query to refer its own results when computing its results. That may sound mind-bending, but it's really just poorly named way to do a "while" loop in SQL.
A WITH RECURSIVE has the form (base-case-query UNION ALL iterative-step-query). The query for the iterative step can refer to itself via the WITH alias. When it does so, it's actually operating on only those records produced by the previous step of the iteration. The iterative-step-query will execute possibly multiple times, stopping only when it doesn't produce any more records.
Here's the WITH RECURSIVE example from the Postgres docs, translated into Python:
all_records = []
previous_step = [1] # base case, i.e. "VALUES (1)."
while previous_step:
all_records.extend(previous_step)
# iterative step, i.e. "SELECT n+1 FROM t WHERE n < 100"
current_step = [n+1 for n in previous_step if n < 100]
previous_step = current_step
The confusing part is that the alias "t" in the SQL example means different things in different places. Outside the WITH RECURSIVE, "t" is equivalent to "all_records" in the example. Within the WITH RECURSIVE definition, "t" is the same as "previous_step".
WITH RECURSIVE allows a query to refer its own results when computing its results. That may sound mind-bending, but it's really just poorly named way to do a "while" loop in SQL.
A WITH RECURSIVE has the form (base-case-query UNION ALL iterative-step-query). The query for the iterative step can refer to itself via the WITH alias. When it does so, it's actually operating on only those records produced by the previous step of the iteration. The iterative-step-query will execute possibly multiple times, stopping only when it doesn't produce any more records.
Here's the WITH RECURSIVE example from the Postgres docs, translated into Python:
The confusing part is that the alias "t" in the SQL example means different things in different places. Outside the WITH RECURSIVE, "t" is equivalent to "all_records" in the example. Within the WITH RECURSIVE definition, "t" is the same as "previous_step".