Did it help? If yes - maybe you can help me? Donate BTC to 19zPa5diT2LZqGtTi8f8bfApLn8rw9zBHx
On 1st of February, Peter Eisentraut committed patch:
Add CREATE RECURSIVE VIEW syntax This is specified in the SQL standard. The CREATE RECURSIVE VIEW specification is transformed into a normal CREATE VIEW statement with a WITH RECURSIVE clause. reviewed by Abhijit Menon-Sen and Stephen Frost
It was committed quite some time ago, but I must have missed it, sorry about that.
This is just a syntax sugar for standard recursive queries, in CTE.
For example, let's imagine a simple recursive query that could be used to generate Fibonacci sequence:
with recursive fib as ( select 0 as a, 1 as b union all select b, a + b from fib where b <= 100 ) select a from fib; a ---- 0 1 1 2 3 5 8 13 21 34 55 89 (12 rows)
If we'd want a view on this query, we could:
create view fib_up_to_100 as with recursive fib as ( select 0 as a, 1 as b union all select b, a + b from fib where b <= 100 ) select a from fib; CREATE VIEW
and then, we can:
select * from fib_up_to_100; a ---- 0 1 1 2 3 5 8 13 21 34 55 89 (12 rows)
What this new patch adds, is that we can specify the same view using different syntax. Like this:
create recursive view fib_up_to_100 (a,b) as select 0 as a, 1 as b union all select b, a + b from fib_up_to_100 where b <= 100 CREATE VIEW
select * from fib_up_to_100; a | b ----+----- 0 | 1 1 | 1 1 | 2 2 | 3 3 | 5 5 | 8 8 | 13 13 | 21 21 | 34 34 | 55 55 | 89 89 | 144 (12 rows)
There are some differences – namely – as I understand, you have to specify columns. And you can't add any additional clauses that would normally be outside of WITH clause. But still – it makes for a simpler query, and perhaps easier understanding of what the view does.
One thing, though – just as documentation suggests – view source is modified. SO the simple syntax doesn't stay anywhere in the DB. Dumped view will look like this:
CREATE VIEW fib_up_to_100 AS WITH RECURSIVE fib_up_to_100(a, b) AS ( SELECT 0 AS a, 1 AS b UNION ALL SELECT fib_up_to_100_1.b, (fib_up_to_100_1.a + fib_up_to_100_1.b) FROM fib_up_to_100 fib_up_to_100_1 WHERE (fib_up_to_100_1.b <= 100) ) SELECT fib_up_to_100.a, fib_up_to_100.b FROM fib_up_to_100;
Still, it's a nice addition, if for nothing else, then for standard compatibility.