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

Commitf4adc41

Browse files
committed
Enhanced cycle mark values
Per SQL:202x draft, in the CYCLE clause of a recursive query, thecycle mark values can be of type boolean and can be omitted, in whichcase they default to TRUE and FALSE.Reviewed-by: Vik Fearing <vik@postgresfriends.org>Discussion:https://www.postgresql.org/message-id/flat/db80ceee-6f97-9b4a-8ee8-3ba0c58e5be2@2ndquadrant.com
1 parent4e90052 commitf4adc41

File tree

7 files changed

+144
-47
lines changed

7 files changed

+144
-47
lines changed

‎doc/src/sgml/queries.sgml

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -2349,14 +2349,13 @@ WITH RECURSIVE search_graph(id, link, data, depth) AS (
23492349
SELECT g.id, g.link, g.data, sg.depth + 1
23502350
FROM graph g, search_graph sg
23512351
WHERE g.id = sg.link
2352-
) <emphasis>CYCLE id SET is_cycleTO true DEFAULT falseUSING path</emphasis>
2352+
) <emphasis>CYCLE id SET is_cycle USING path</emphasis>
23532353
SELECT * FROM search_graph;
23542354
</programlisting>
23552355
and it will be internally rewritten to the above form. The
23562356
<literal>CYCLE</literal> clause specifies first the list of columns to
23572357
track for cycle detection, then a column name that will show whether a
2358-
cycle has been detected, then two values to use in that column for the yes
2359-
and no cases, and finally the name of another column that will track the
2358+
cycle has been detected, and finally the name of another column that will track the
23602359
path. The cycle and path columns will implicitly be added to the output
23612360
rows of the CTE.
23622361
</para>

‎doc/src/sgml/ref/select.sgml

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -74,7 +74,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
7474

7575
<replaceable class="parameter">with_query_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( <replaceable class="parameter">select</replaceable> | <replaceable class="parameter">values</replaceable> | <replaceable class="parameter">insert</replaceable> | <replaceable class="parameter">update</replaceable> | <replaceable class="parameter">delete</replaceable> )
7676
[ SEARCH { BREADTH | DEPTH } FIRST BY <replaceable>column_name</replaceable> [, ...] SET <replaceable>search_seq_col_name</replaceable> ]
77-
[ CYCLE <replaceable>column_name</replaceable> [, ...] SET <replaceable>cycle_mark_col_name</replaceable> TO <replaceable>cycle_mark_value</replaceable> DEFAULT <replaceable>cycle_mark_default</replaceable> USING <replaceable>cycle_path_col_name</replaceable> ]
77+
[ CYCLE <replaceable>column_name</replaceable> [, ...] SET <replaceable>cycle_mark_col_name</replaceable>[TO <replaceable>cycle_mark_value</replaceable> DEFAULT <replaceable>cycle_mark_default</replaceable> ] USING <replaceable>cycle_path_col_name</replaceable> ]
7878

7979
TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
8080
</synopsis>
@@ -302,8 +302,10 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
302302
been detected. <replaceable>cycle_mark_value</replaceable> and
303303
<replaceable>cycle_mark_default</replaceable> must be constants and they
304304
must be coercible to a common data type, and the data type must have an
305-
inequality operator. (The SQL standard requires that they be character
306-
strings, but PostgreSQL does not require that.) Furthermore, a column
305+
inequality operator. (The SQL standard requires that they be Boolean
306+
constants or character strings, but PostgreSQL does not require that.) By
307+
default, <literal>TRUE</literal> and <literal>FALSE</literal> (of type
308+
<type>boolean</type>) are used. Furthermore, a column
307309
named <replaceable>cycle_path_col_name</replaceable> will be added to the
308310
result column list of the <literal>WITH</literal> query. This column is
309311
used internally for tracking visited rows. See <xref

‎src/backend/catalog/sql_features.txt

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -425,6 +425,7 @@ T121WITH (excluding RECURSIVE) in query expressionYES
425425
T122WITH (excluding RECURSIVE) in subqueryYES
426426
T131Recursive queryYES
427427
T132Recursive query in subqueryYES
428+
T133Enhanced cycle mark valuesYESSQL:202x draft
428429
T141SIMILAR predicateYES
429430
T151DISTINCT predicateYES
430431
T152DISTINCT predicate with negationYES

‎src/backend/parser/gram.y

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -11442,6 +11442,17 @@ opt_cycle_clause:
1144211442
n->location =@1;
1144311443
$$ = (Node *) n;
1144411444
}
11445+
|CYCLEcolumnListSETColIdUSINGColId
11446+
{
11447+
CTECycleClause *n = makeNode(CTECycleClause);
11448+
n->cycle_col_list =$2;
11449+
n->cycle_mark_column =$4;
11450+
n->cycle_mark_value = makeBoolAConst(true, -1);
11451+
n->cycle_mark_default = makeBoolAConst(false, -1);
11452+
n->cycle_path_column =$6;
11453+
n->location =@1;
11454+
$$ = (Node *) n;
11455+
}
1144511456
|/*EMPTY*/
1144611457
{
1144711458
$$ =NULL;

‎src/backend/utils/adt/ruleutils.c

Lines changed: 15 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -5208,10 +5208,21 @@ get_with_clause(Query *query, deparse_context *context)
52085208
}
52095209

52105210
appendStringInfo(buf," SET %s",quote_identifier(cte->cycle_clause->cycle_mark_column));
5211-
appendStringInfoString(buf," TO ");
5212-
get_rule_expr(cte->cycle_clause->cycle_mark_value,context, false);
5213-
appendStringInfoString(buf," DEFAULT ");
5214-
get_rule_expr(cte->cycle_clause->cycle_mark_default,context, false);
5211+
5212+
{
5213+
Const*cmv=castNode(Const,cte->cycle_clause->cycle_mark_value);
5214+
Const*cmd=castNode(Const,cte->cycle_clause->cycle_mark_default);
5215+
5216+
if (!(cmv->consttype==BOOLOID&& !cmv->constisnull&&DatumGetBool(cmv->constvalue)== true&&
5217+
cmd->consttype==BOOLOID&& !cmd->constisnull&&DatumGetBool(cmd->constvalue)== false))
5218+
{
5219+
appendStringInfoString(buf," TO ");
5220+
get_rule_expr(cte->cycle_clause->cycle_mark_value,context, false);
5221+
appendStringInfoString(buf," DEFAULT ");
5222+
get_rule_expr(cte->cycle_clause->cycle_mark_default,context, false);
5223+
}
5224+
}
5225+
52155226
appendStringInfo(buf," USING %s",quote_identifier(cte->cycle_clause->cycle_path_column));
52165227
}
52175228

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

Lines changed: 89 additions & 28 deletions
Original file line numberDiff line numberDiff line change
@@ -951,7 +951,7 @@ with recursive search_graph(f, t, label) as (
951951
select g.*
952952
from graph g, search_graph sg
953953
where g.f = sg.t
954-
) cycle f, t set is_cycleto true default falseusing path
954+
) cycle f, t set is_cycle using path
955955
select * from search_graph;
956956
f | t | label | is_cycle | path
957957
---+---+------------+----------+-------------------------------------------
@@ -1071,7 +1071,7 @@ with recursive a as (
10711071
select 1 as b
10721072
union all
10731073
select * from a
1074-
) cycle b set cto true default falseusing p
1074+
) cycle b set c using p
10751075
select * from a;
10761076
b | c | p
10771077
---+---+-----------
@@ -1087,7 +1087,7 @@ with recursive search_graph(f, t, label) as (
10871087
from graph g, search_graph sg
10881088
where g.f = sg.t
10891089
) search depth first by f, t set seq
1090-
cycle f, t set is_cycleto true default falseusing path
1090+
cycle f, t set is_cycle using path
10911091
select * from search_graph;
10921092
f | t | label | seq | is_cycle | path
10931093
---+---+------------+-------------------------------------------+----------+-------------------------------------------
@@ -1125,7 +1125,7 @@ with recursive search_graph(f, t, label) as (
11251125
from graph g, search_graph sg
11261126
where g.f = sg.t
11271127
) search breadth first by f, t set seq
1128-
cycle f, t set is_cycleto true default falseusing path
1128+
cycle f, t set is_cycle using path
11291129
select * from search_graph;
11301130
f | t | label | seq | is_cycle | path
11311131
---+---+------------+---------+----------+-------------------------------------------
@@ -1163,10 +1163,10 @@ with recursive search_graph(f, t, label) as (
11631163
select g.*
11641164
from graph g, search_graph sg
11651165
where g.f = sg.t
1166-
) cycle foo, tar set is_cycleto true default falseusing path
1166+
) cycle foo, tar set is_cycle using path
11671167
select * from search_graph;
11681168
ERROR: cycle column "foo" not in WITH query column list
1169-
LINE 7: ) cycle foo, tar set is_cycleto true default falseusingpa...
1169+
LINE 7: ) cycle foo, tar set is_cycle usingpath
11701170
^
11711171
with recursive search_graph(f, t, label) as (
11721172
select * from graph g
@@ -1257,38 +1257,99 @@ ERROR: search_sequence column name and cycle path column name are the same
12571257
LINE 7: ) search depth first by f, t set foo
12581258
^
12591259
-- test ruleutils and view expansion
1260-
create temp viewv_cycle as
1260+
create temp viewv_cycle1 as
12611261
with recursive search_graph(f, t, label) as (
12621262
select * from graph g
12631263
union all
12641264
select g.*
12651265
from graph g, search_graph sg
12661266
where g.f = sg.t
1267-
) cycle f, t set is_cycleto true default falseusing path
1267+
) cycle f, t set is_cycle using path
12681268
select f, t, label from search_graph;
1269-
select pg_get_viewdef('v_cycle');
1270-
pg_get_viewdef
1271-
--------------------------------------------------------------------
1272-
WITH RECURSIVE search_graph(f, t, label) AS ( +
1273-
SELECT g.f, +
1274-
g.t, +
1275-
g.label +
1276-
FROM graph g +
1277-
UNION ALL +
1278-
SELECT g.f, +
1279-
g.t, +
1280-
g.label +
1281-
FROM graph g, +
1282-
search_graph sg +
1283-
WHERE (g.f = sg.t) +
1284-
) CYCLE f, t SET is_cycle TO true DEFAULT false USING path+
1285-
SELECT search_graph.f, +
1286-
search_graph.t, +
1287-
search_graph.label +
1269+
create temp view v_cycle2 as
1270+
with recursive search_graph(f, t, label) as (
1271+
select * from graph g
1272+
union all
1273+
select g.*
1274+
from graph g, search_graph sg
1275+
where g.f = sg.t
1276+
) cycle f, t set is_cycle to 'Y' default 'N' using path
1277+
select f, t, label from search_graph;
1278+
select pg_get_viewdef('v_cycle1');
1279+
pg_get_viewdef
1280+
------------------------------------------------
1281+
WITH RECURSIVE search_graph(f, t, label) AS (+
1282+
SELECT g.f, +
1283+
g.t, +
1284+
g.label +
1285+
FROM graph g +
1286+
UNION ALL +
1287+
SELECT g.f, +
1288+
g.t, +
1289+
g.label +
1290+
FROM graph g, +
1291+
search_graph sg +
1292+
WHERE (g.f = sg.t) +
1293+
) CYCLE f, t SET is_cycle USING path +
1294+
SELECT search_graph.f, +
1295+
search_graph.t, +
1296+
search_graph.label +
12881297
FROM search_graph;
12891298
(1 row)
12901299

1291-
select * from v_cycle;
1300+
select pg_get_viewdef('v_cycle2');
1301+
pg_get_viewdef
1302+
-----------------------------------------------------------------------------
1303+
WITH RECURSIVE search_graph(f, t, label) AS ( +
1304+
SELECT g.f, +
1305+
g.t, +
1306+
g.label +
1307+
FROM graph g +
1308+
UNION ALL +
1309+
SELECT g.f, +
1310+
g.t, +
1311+
g.label +
1312+
FROM graph g, +
1313+
search_graph sg +
1314+
WHERE (g.f = sg.t) +
1315+
) CYCLE f, t SET is_cycle TO 'Y'::text DEFAULT 'N'::text USING path+
1316+
SELECT search_graph.f, +
1317+
search_graph.t, +
1318+
search_graph.label +
1319+
FROM search_graph;
1320+
(1 row)
1321+
1322+
select * from v_cycle1;
1323+
f | t | label
1324+
---+---+------------
1325+
1 | 2 | arc 1 -> 2
1326+
1 | 3 | arc 1 -> 3
1327+
2 | 3 | arc 2 -> 3
1328+
1 | 4 | arc 1 -> 4
1329+
4 | 5 | arc 4 -> 5
1330+
5 | 1 | arc 5 -> 1
1331+
1 | 2 | arc 1 -> 2
1332+
1 | 3 | arc 1 -> 3
1333+
1 | 4 | arc 1 -> 4
1334+
2 | 3 | arc 2 -> 3
1335+
4 | 5 | arc 4 -> 5
1336+
5 | 1 | arc 5 -> 1
1337+
1 | 2 | arc 1 -> 2
1338+
1 | 3 | arc 1 -> 3
1339+
1 | 4 | arc 1 -> 4
1340+
2 | 3 | arc 2 -> 3
1341+
4 | 5 | arc 4 -> 5
1342+
5 | 1 | arc 5 -> 1
1343+
1 | 2 | arc 1 -> 2
1344+
1 | 3 | arc 1 -> 3
1345+
1 | 4 | arc 1 -> 4
1346+
2 | 3 | arc 2 -> 3
1347+
4 | 5 | arc 4 -> 5
1348+
5 | 1 | arc 5 -> 1
1349+
2 | 3 | arc 2 -> 3
1350+
(25 rows)
1351+
1352+
select * from v_cycle2;
12921353
f | t | label
12931354
---+---+------------
12941355
1 | 2 | arc 1 -> 2

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

Lines changed: 21 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -509,7 +509,7 @@ with recursive search_graph(f, t, label) as (
509509
select g.*
510510
from graph g, search_graph sg
511511
whereg.f=sg.t
512-
) cycle f, tset is_cycleto true default falseusingpath
512+
) cycle f, tset is_cycle usingpath
513513
select*from search_graph;
514514

515515
with recursive search_graph(f, t, label)as (
@@ -545,7 +545,7 @@ with recursive a as (
545545
select1as b
546546
union all
547547
select*from a
548-
) cycle bset cto true default falseusing p
548+
) cycle bset c using p
549549
select*from a;
550550

551551
-- search+cycle
@@ -556,7 +556,7 @@ with recursive search_graph(f, t, label) as (
556556
from graph g, search_graph sg
557557
whereg.f=sg.t
558558
) search depth first by f, tset seq
559-
cycle f, tset is_cycleto true default falseusingpath
559+
cycle f, tset is_cycle usingpath
560560
select*from search_graph;
561561

562562
with recursive search_graph(f, t, label)as (
@@ -566,7 +566,7 @@ with recursive search_graph(f, t, label) as (
566566
from graph g, search_graph sg
567567
whereg.f=sg.t
568568
) search breadth first by f, tset seq
569-
cycle f, tset is_cycleto true default falseusingpath
569+
cycle f, tset is_cycle usingpath
570570
select*from search_graph;
571571

572572
-- various syntax errors
@@ -576,7 +576,7 @@ with recursive search_graph(f, t, label) as (
576576
select g.*
577577
from graph g, search_graph sg
578578
whereg.f=sg.t
579-
) cycle foo, tarset is_cycleto true default falseusingpath
579+
) cycle foo, tarset is_cycle usingpath
580580
select*from search_graph;
581581

582582
with recursive search_graph(f, t, label)as (
@@ -654,19 +654,31 @@ with recursive search_graph(f, t, label) as (
654654
select*from search_graph;
655655

656656
-- test ruleutils and view expansion
657-
create temp viewv_cycleas
657+
create temp viewv_cycle1as
658658
with recursive search_graph(f, t, label)as (
659659
select*from graph g
660660
union all
661661
select g.*
662662
from graph g, search_graph sg
663663
whereg.f=sg.t
664-
) cycle f, tset is_cycle to true default false usingpath
664+
) cycle f, tset is_cycle usingpath
665+
select f, t, labelfrom search_graph;
666+
667+
create temp view v_cycle2as
668+
with recursive search_graph(f, t, label)as (
669+
select*from graph g
670+
union all
671+
select g.*
672+
from graph g, search_graph sg
673+
whereg.f=sg.t
674+
) cycle f, tset is_cycle to'Y' default'N' usingpath
665675
select f, t, labelfrom search_graph;
666676

667-
select pg_get_viewdef('v_cycle');
677+
select pg_get_viewdef('v_cycle1');
678+
select pg_get_viewdef('v_cycle2');
668679

669-
select*from v_cycle;
680+
select*from v_cycle1;
681+
select*from v_cycle2;
670682

671683
--
672684
-- test multiple WITH queries

0 commit comments

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