LivingColors LivingColors - 5 months ago 22
SQL Question

Possible explanation on WITH RECURSIVE Query Postgres

I have been reading around With Query in Postgres. And this is what I'm surprised with

WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;


I'm not able to understand how does the evaluation of the query work.


  • t(n) it sound like a function with a parameter. how does the value of n is passed.



Any insight on how the break down happen of the recursive statement in SQL.

Answer

This is called a common table expression and is a way of expressing a recursive query in SQL:

t(n) defines the name of the CTE as t, with a single column named n. It's similar to an alias for a derived table:

select ... 
from (
  ...
) as t(n);

The recursion starts with the value 1 (that's the values (1) part) and then recursively adds one to it until the 99 is reached. So it generates the numbers from 1 to 99. Then final query then sums up all those numbers.

n is a column name, not a "variable" and the "assignment" happens in the same way as any data retrieval.

WITH RECURSIVE t(n) AS (
    VALUES (1) --<< this is the recursion "root"
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100 --<< this is the "recursive part"
)
SELECT sum(n) FROM t;

If you "unroll" the recursion (which in fact is an iteration) then you'd wind up with something like this:

select x.n + 1
from (
  select x.n + 1
  from (
    select x.n + 1
    from (
      select x.n + 1
      from (
         values (1)
      ) as x(n) 
    ) as x(n)
  ) as x(n)
) as x(n)

More details in the manual:
https://www.postgresql.org/docs/current/static/queries-with.html

Comments