March 4th, 2013 by depesz | Tags: , , , , , , | No comments »
Did it help? If yes - maybe you can help me?

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```

And now:

```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.