Waiting for 9.3 – Add CREATE RECURSIVE VIEW syntax

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.