Skip to content

Commit f4adc41

Browse files
peterepetere
committed
Enhanced cycle mark values
Per SQL:202x draft, in the CYCLE clause of a recursive query, the cycle mark values can be of type boolean and can be omitted, in which case 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 parent 4e90052 commit f4adc41

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_cycle TO true DEFAULT false USING 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 @@ T121 WITH (excluding RECURSIVE) in query expression YES
425425
T122 WITH (excluding RECURSIVE) in subquery YES
426426
T131 Recursive query YES
427427
T132 Recursive query in subquery YES
428+
T133 Enhanced cycle mark values YES SQL:202x draft
428429
T141 SIMILAR predicate YES
429430
T151 DISTINCT predicate YES
430431
T152 DISTINCT predicate with negation YES

‎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+
| CYCLE columnList SET ColId USING ColId
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_cycle to true default false using 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 c to true default false using 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_cycle to true default false using 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_cycle to true default false using 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_cycle to true default false using 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_cycle to true default false using pa...
1169+
LINE 7: ) cycle foo, tar set is_cycle using path
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 view v_cycle as
1260+
create temp view v_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_cycle to true default false using 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
where g.f = sg.t
512-
) cycle f, t set is_cycle to true default false using path
512+
) cycle f, t set is_cycle using path
513513
select * from search_graph;
514514

515515
with recursive search_graph(f, t, label) as (
@@ -545,7 +545,7 @@ with recursive a as (
545545
select 1 as b
546546
union all
547547
select * from a
548-
) cycle b set c to true default false using p
548+
) cycle b set 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
where g.f = sg.t
558558
) search depth first by f, t set seq
559-
cycle f, t set is_cycle to true default false using path
559+
cycle f, t set is_cycle using path
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
where g.f = sg.t
568568
) search breadth first by f, t set seq
569-
cycle f, t set is_cycle to true default false using path
569+
cycle f, t set is_cycle using path
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
where g.f = sg.t
579-
) cycle foo, tar set is_cycle to true default false using path
579+
) cycle foo, tar set is_cycle using path
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 view v_cycle as
657+
create temp view v_cycle1 as
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
where g.f = sg.t
664-
) cycle f, t set is_cycle to true default false using path
664+
) cycle f, t set is_cycle using path
665+
select f, t, label from search_graph;
666+
667+
create temp view v_cycle2 as
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+
where g.f = sg.t
674+
) cycle f, t set is_cycle to 'Y' default 'N' using path
665675
select f, t, label from 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)