March 4th, 2013 by depesz | Tags: , , , , , , | No comments »
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

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.

Leave a comment