LivingColors - 9 months ago 42

SQL Question

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