Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit5839052

Browse files
committed
Add CREATE RECURSIVE VIEW syntax
This is specified in the SQL standard. The CREATE RECURSIVE VIEWspecification is transformed into a normal CREATE VIEW statement with aWITH RECURSIVE clause.reviewed by Abhijit Menon-Sen and Stephen Frost
1 parentb1980f6 commit5839052

File tree

4 files changed

+158
-1
lines changed

4 files changed

+158
-1
lines changed

‎doc/src/sgml/ref/create_view.sgml

Lines changed: 28 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24-
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW <replaceable class="PARAMETER">name</replaceable> [ ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) ]
24+
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ][ RECURSIVE ]VIEW <replaceable class="PARAMETER">name</replaceable> [ ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) ]
2525
[ WITH ( <replaceable class="PARAMETER">view_option_name</replaceable> [= <replaceable class="PARAMETER">view_option_value</replaceable>] [, ... ] ) ]
2626
AS <replaceable class="PARAMETER">query</replaceable>
2727
</synopsis>
@@ -80,6 +80,23 @@ CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW <replaceable class="PARAMETER">n
8080
</listitem>
8181
</varlistentry>
8282

83+
<varlistentry>
84+
<term><literal>RECURSIVE</></term>
85+
<listitem>
86+
<para>
87+
Creates a recursive view. The syntax
88+
<synopsis>
89+
CREATE RECURSIVE VIEW <replaceable>name</> (<replaceable>columns</>) AS SELECT <replaceable>...</>;
90+
</synopsis>
91+
is equivalent to
92+
<synopsis>
93+
CREATE VIEW <replaceable>name</> AS WITH RECURSIVE <replaceable>name</> (<replaceable>columns</>) AS (SELECT <replaceable>...</>) SELECT <replaceable>columns</> FROM <replaceable>name</>;
94+
</synopsis>
95+
A view column list must be specified for a recursive view.
96+
</para>
97+
</listitem>
98+
</varlistentry>
99+
83100
<varlistentry>
84101
<term><replaceable class="parameter">name</replaceable></term>
85102
<listitem>
@@ -282,6 +299,16 @@ CREATE VIEW comedies AS
282299
<literal>*</> was used to create the view, columns added later to
283300
the table will not be part of the view.
284301
</para>
302+
303+
<para>
304+
Create a recursive view consisting of the numbers from 1 to 100:
305+
<programlisting>
306+
CREATE RECURSIVE VIEW nums_1_100 (n) AS
307+
VALUES (1)
308+
UNION ALL
309+
SELECT n+1 FROM nums_1_100 WHERE n < 100;
310+
</programlisting>
311+
</para>
285312
</refsect1>
286313

287314
<refsect1>

‎src/backend/parser/gram.y

Lines changed: 85 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -164,6 +164,7 @@ static void SplitColQualList(List *qualList,
164164
staticvoidprocessCASbits(int cas_bits,int location,constchar *constrType,
165165
bool *deferrable,bool *initdeferred,bool *not_valid,
166166
bool *no_inherit,core_yyscan_t yyscanner);
167+
static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
167168

168169
%}
169170

@@ -7839,6 +7840,30 @@ ViewStmt: CREATE OptTemp VIEW qualified_name opt_column_list opt_reloptions
78397840
n->options =$8;
78407841
$$ = (Node *) n;
78417842
}
7843+
|CREATEOptTempRECURSIVEVIEWqualified_name'('columnList')'opt_reloptions
7844+
ASSelectStmt
7845+
{
7846+
ViewStmt *n = makeNode(ViewStmt);
7847+
n->view =$5;
7848+
n->view->relpersistence =$2;
7849+
n->aliases =$7;
7850+
n->query = makeRecursiveViewSelect(n->view->relname, n->aliases,$11);
7851+
n->replace =false;
7852+
n->options =$9;
7853+
$$ = (Node *) n;
7854+
}
7855+
|CREATEORREPLACEOptTempRECURSIVEVIEWqualified_name'('columnList')'opt_reloptions
7856+
ASSelectStmt
7857+
{
7858+
ViewStmt *n = makeNode(ViewStmt);
7859+
n->view =$7;
7860+
n->view->relpersistence =$4;
7861+
n->aliases =$9;
7862+
n->query = makeRecursiveViewSelect(n->view->relname, n->aliases,$13);
7863+
n->replace =true;
7864+
n->options =$11;
7865+
$$ = (Node *) n;
7866+
}
78427867
;
78437868

78447869
opt_check_option:
@@ -13570,6 +13595,66 @@ processCASbits(int cas_bits, int location, const char *constrType,
1357013595
}
1357113596
}
1357213597

13598+
/*----------
13599+
* Recursive view transformation
13600+
*
13601+
* Convert
13602+
*
13603+
* CREATE RECURSIVE VIEW relname (aliases) AS query
13604+
*
13605+
* to
13606+
*
13607+
* CREATE VIEW relname (aliases) AS
13608+
* WITH RECURSIVE relname (aliases) AS (query)
13609+
* SELECT aliases FROM relname
13610+
*
13611+
* Actually, just the WITH ... part, which is then inserted into the original
13612+
* view definition as the query.
13613+
* ----------
13614+
*/
13615+
static Node *
13616+
makeRecursiveViewSelect(char *relname, List *aliases, Node *query)
13617+
{
13618+
SelectStmt *s = makeNode(SelectStmt);
13619+
WithClause *w = makeNode(WithClause);
13620+
CommonTableExpr *cte = makeNode(CommonTableExpr);
13621+
List *tl = NIL;
13622+
ListCell *lc;
13623+
13624+
/* create common table expression*/
13625+
cte->ctename = relname;
13626+
cte->aliascolnames = aliases;
13627+
cte->ctequery = query;
13628+
cte->location = -1;
13629+
13630+
/* create WITH clause and attach CTE*/
13631+
w->recursive = true;
13632+
w->ctes = list_make1(cte);
13633+
w->location = -1;
13634+
13635+
/* create target list for the new SELECT from the alias list of the
13636+
* recursive view specification*/
13637+
foreach (lc, aliases)
13638+
{
13639+
ResTarget *rt = makeNode(ResTarget);
13640+
13641+
rt->name = NULL;
13642+
rt->indirection = NIL;
13643+
rt->val = makeColumnRef(strVal(lfirst(lc)), NIL, -1, 0);
13644+
rt->location = -1;
13645+
13646+
tl = lappend(tl, rt);
13647+
}
13648+
13649+
/* create new SELECT combining WITH clause, target list, and fake FROM
13650+
* clause*/
13651+
s->withClause = w;
13652+
s->targetList = tl;
13653+
s->fromClause = list_make1(makeRangeVar(NULL, relname, -1));
13654+
13655+
return (Node *) s;
13656+
}
13657+
1357313658
/* parser_init()
1357413659
* Initialize to parse one query string
1357513660
*/

‎src/test/regress/expected/with.out

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -49,6 +49,36 @@ SELECT * FROM t;
4949
5
5050
(5 rows)
5151

52+
-- recursive view
53+
CREATE RECURSIVE VIEW nums (n) AS
54+
VALUES (1)
55+
UNION ALL
56+
SELECT n+1 FROM nums WHERE n < 5;
57+
SELECT * FROM nums;
58+
n
59+
---
60+
1
61+
2
62+
3
63+
4
64+
5
65+
(5 rows)
66+
67+
CREATE OR REPLACE RECURSIVE VIEW nums (n) AS
68+
VALUES (1)
69+
UNION ALL
70+
SELECT n+1 FROM nums WHERE n < 6;
71+
SELECT * FROM nums;
72+
n
73+
---
74+
1
75+
2
76+
3
77+
4
78+
5
79+
6
80+
(6 rows)
81+
5282
-- This is an infinite loop with UNION ALL, but not with UNION
5383
WITH RECURSIVE t(n) AS (
5484
SELECT 1

‎src/test/regress/sql/with.sql

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -31,6 +31,21 @@ UNION ALL
3131
)
3232
SELECT*FROM t;
3333

34+
-- recursive view
35+
CREATE RECURSIVE VIEW nums (n)AS
36+
VALUES (1)
37+
UNION ALL
38+
SELECT n+1FROM numsWHERE n<5;
39+
40+
SELECT*FROM nums;
41+
42+
CREATEOR REPLACE RECURSIVE VIEW nums (n)AS
43+
VALUES (1)
44+
UNION ALL
45+
SELECT n+1FROM numsWHERE n<6;
46+
47+
SELECT*FROM nums;
48+
3449
-- This is an infinite loop with UNION ALL, but not with UNION
3550
WITH RECURSIVE t(n)AS (
3651
SELECT1

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp