From 583905269378bf41c24585773885b1e226a998ce Mon Sep 17 00:00:00 2001
From: Peter Eisentraut
Date: Thu, 31 Jan 2013 22:31:58 -0500
Subject: 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
---
 src/test/regress/expected/with.out | 30 ++++++++++++++++++++++++++++++
 src/test/regress/sql/with.sql      | 15 +++++++++++++++
 2 files changed, 45 insertions(+)

(limited to 'src/test')

diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index b98ca630ee9..272118f7b70 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -49,6 +49,36 @@ SELECT * FROM t;
  5
 (5 rows)
 
+-- recursive view
+CREATE RECURSIVE VIEW nums (n) AS
+    VALUES (1)
+UNION ALL
+    SELECT n+1 FROM nums WHERE n < 5;
+SELECT * FROM nums;
+ n 
+---
+ 1
+ 2
+ 3
+ 4
+ 5
+(5 rows)
+
+CREATE OR REPLACE RECURSIVE VIEW nums (n) AS
+    VALUES (1)
+UNION ALL
+    SELECT n+1 FROM nums WHERE n < 6;
+SELECT * FROM nums;
+ n 
+---
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+(6 rows)
+
 -- This is an infinite loop with UNION ALL, but not with UNION
 WITH RECURSIVE t(n) AS (
     SELECT 1
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index 4ff852736bc..c7163699576 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -31,6 +31,21 @@ UNION ALL
 )
 SELECT * FROM t;
 
+-- recursive view
+CREATE RECURSIVE VIEW nums (n) AS
+    VALUES (1)
+UNION ALL
+    SELECT n+1 FROM nums WHERE n < 5;
+
+SELECT * FROM nums;
+
+CREATE OR REPLACE RECURSIVE VIEW nums (n) AS
+    VALUES (1)
+UNION ALL
+    SELECT n+1 FROM nums WHERE n < 6;
+
+SELECT * FROM nums;
+
 -- This is an infinite loop with UNION ALL, but not with UNION
 WITH RECURSIVE t(n) AS (
     SELECT 1
-- 
cgit v1.2.3