I noticed we don't implement the recursive view syntax, even though it's
part of the standard SQL feature set for recursive queries. Here is a
patch to add that. It basically converts
CREATE RECURSIVE VIEW name (columns) AS SELECT ...;
to
CREATE VIEW name AS WITH RECURSIVE name (columns) AS (SELECT ...) SELECT
columns FROM name;
diff --git a/doc/src/sgml/ref/create_view.sgml b/doc/src/sgml/ref/create_view.sgml
index 838bf48..c13f3ec 100644
--- a/doc/src/sgml/ref/create_view.sgml
+++ b/doc/src/sgml/ref/create_view.sgml
@@ -21,7 +21,7 @@
<refsynopsisdiv>
<synopsis>
-CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW <replaceable class="PARAMETER">name</replaceable> [ ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) ]
+CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW <replaceable class="PARAMETER">name</replaceable> [ ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) ]
[ WITH ( <replaceable class="PARAMETER">view_option_name</replaceable> [= <replaceable class="PARAMETER">view_option_value</replaceable>] [, ... ] ) ]
AS <replaceable class="PARAMETER">query</replaceable>
</synopsis>
@@ -81,6 +81,23 @@ <title>Parameters</title>
</varlistentry>
<varlistentry>
+ <term><literal>RECURSIVE</></term>
+ <listitem>
+ <para>
+ Creates a recursive view. The syntax
+<synopsis>
+CREATE RECURSIVE VIEW <replaceable>name</> (<replaceable>columns</>) AS SELECT <replaceable>...</>;
+</synopsis>
+ is equivalent to
+<synopsis>
+CREATE VIEW <replaceable>name</> AS WITH RECURSIVE <replaceable>name</> (<replaceable>columns</>) AS (SELECT <replaceable>...</>) SELECT <replaceable>columns</> FROM <replaceable>name</>;
+</synopsis>
+ A view column list must be specified for a recursive view.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="parameter">name</replaceable></term>
<listitem>
<para>
@@ -191,6 +208,16 @@ <title>Examples</title>
<literal>*</> was used to create the view, columns added later to
the table will not be part of the view.
</para>
+
+ <para>
+ Create a recursive view consisting of the numbers from 1 to 100:
+<programlisting>
+CREATE RECURSIVE VIEW nums_1_100 (n) AS
+ VALUES (1)
+UNION ALL
+ SELECT n+1 FROM nums_1_100 WHERE n < 100;
+</programlisting>
+ </para>
</refsect1>
<refsect1>
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index e4ff76e..159096a 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -164,6 +164,7 @@ static void SplitColQualList(List *qualList,
static void processCASbits(int cas_bits, int location, const char *constrType,
bool *deferrable, bool *initdeferred, bool *not_valid,
bool *no_inherit, core_yyscan_t yyscanner);
+static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%}
@@ -7834,6 +7835,30 @@ ViewStmt: CREATE OptTemp VIEW qualified_name opt_column_list opt_reloptions
n->options = $8;
$$ = (Node *) n;
}
+ | CREATE OptTemp RECURSIVE VIEW qualified_name '(' columnList ')' opt_reloptions
+ AS SelectStmt
+ {
+ ViewStmt *n = makeNode(ViewStmt);
+ n->view = $5;
+ n->view->relpersistence = $2;
+ n->aliases = $7;
+ n->query = makeRecursiveViewSelect(n->view->relname, n->aliases, $11);
+ n->replace = false;
+ n->options = $9;
+ $$ = (Node *) n;
+ }
+ | CREATE OR REPLACE OptTemp RECURSIVE VIEW qualified_name '(' columnList ')' opt_reloptions
+ AS SelectStmt
+ {
+ ViewStmt *n = makeNode(ViewStmt);
+ n->view = $7;
+ n->view->relpersistence = $4;
+ n->aliases = $9;
+ n->query = makeRecursiveViewSelect(n->view->relname, n->aliases, $13);
+ n->replace = true;
+ n->options = $11;
+ $$ = (Node *) n;
+ }
;
opt_check_option:
@@ -13541,6 +13566,60 @@ processCASbits(int cas_bits, int location, const char *constrType,
}
}
+/*----------
+ * Recursive view transformation
+ *
+ * Convert
+ *
+ * CREATE RECURSIVE VIEW relname (aliases) AS query
+ *
+ * to
+ *
+ * CREATE VIEW relname (aliases) AS
+ * WITH RECURSIVE relname (aliases) AS (query)
+ * SELECT aliases FROM relname
+ *
+ * Actually, just the WITH ... part, which is then inserted into the original
+ * view definition as the query.
+ * ----------
+ */
+static Node *
+makeRecursiveViewSelect(char *relname, List *aliases, Node *query)
+{
+ SelectStmt *s = makeNode(SelectStmt);
+ WithClause *w = makeNode(WithClause);
+ CommonTableExpr *cte = makeNode(CommonTableExpr);
+ List *tl = NIL;
+ ListCell *lc;
+
+ cte->ctename = relname;
+ cte->aliascolnames = aliases;
+ cte->ctequery = query;
+ cte->location = -1;
+
+ w->recursive = true;
+ w->ctes = list_make1(cte);
+ w->location = -1;
+
+ foreach (lc, aliases)
+ {
+ ResTarget *rt = makeNode(ResTarget);
+
+ rt->name = NULL;
+ rt->indirection = NIL;
+ rt->val = makeColumnRef(strVal(lfirst(lc)), NIL, -1, 0);
+ rt->location = -1;
+
+ tl = lappend(tl, rt);
+ }
+
+ s->targetList = tl;
+ s->fromClause = list_make1(makeRangeVar(NULL, relname, -1));
+ s->withClause = w;
+
+ return (Node *) s;
+}
+
/* parser_init()
* Initialize to parse one query string
*/
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index b98ca63..272118f 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 4ff8527..c716369 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -31,6 +31,21 @@
)
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
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
