aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/backend/catalog/sql_features.txt1
-rw-r--r--src/backend/parser/gram.y11
-rw-r--r--src/backend/utils/adt/ruleutils.c19
-rw-r--r--src/test/regress/expected/with.out117
-rw-r--r--src/test/regress/sql/with.sql30
5 files changed, 137 insertions, 41 deletions
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index a24387c1e76..ab0895ce3c8 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -425,6 +425,7 @@ T121 WITH (excluding RECURSIVE) in query expression YES
T122 WITH (excluding RECURSIVE) in subquery YES
T131 Recursive query YES
T132 Recursive query in subquery YES
+T133 Enhanced cycle mark values YES SQL:202x draft
T141 SIMILAR predicate YES
T151 DISTINCT predicate YES
T152 DISTINCT predicate with negation YES
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index dd72a9fc3c4..652be0b96da 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11442,6 +11442,17 @@ opt_cycle_clause:
n->location = @1;
$$ = (Node *) n;
}
+ | CYCLE columnList SET ColId USING ColId
+ {
+ CTECycleClause *n = makeNode(CTECycleClause);
+ n->cycle_col_list = $2;
+ n->cycle_mark_column = $4;
+ n->cycle_mark_value = makeBoolAConst(true, -1);
+ n->cycle_mark_default = makeBoolAConst(false, -1);
+ n->cycle_path_column = $6;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
| /*EMPTY*/
{
$$ = NULL;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 4a9244f4f66..879288c1394 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -5208,10 +5208,21 @@ get_with_clause(Query *query, deparse_context *context)
}
appendStringInfo(buf, " SET %s", quote_identifier(cte->cycle_clause->cycle_mark_column));
- appendStringInfoString(buf, " TO ");
- get_rule_expr(cte->cycle_clause->cycle_mark_value, context, false);
- appendStringInfoString(buf, " DEFAULT ");
- get_rule_expr(cte->cycle_clause->cycle_mark_default, context, false);
+
+ {
+ Const *cmv = castNode(Const, cte->cycle_clause->cycle_mark_value);
+ Const *cmd = castNode(Const, cte->cycle_clause->cycle_mark_default);
+
+ if (!(cmv->consttype == BOOLOID && !cmv->constisnull && DatumGetBool(cmv->constvalue) == true &&
+ cmd->consttype == BOOLOID && !cmd->constisnull && DatumGetBool(cmd->constvalue) == false))
+ {
+ appendStringInfoString(buf, " TO ");
+ get_rule_expr(cte->cycle_clause->cycle_mark_value, context, false);
+ appendStringInfoString(buf, " DEFAULT ");
+ get_rule_expr(cte->cycle_clause->cycle_mark_default, context, false);
+ }
+ }
+
appendStringInfo(buf, " USING %s", quote_identifier(cte->cycle_clause->cycle_path_column));
}
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index a7a652822c9..9a6b716ddcc 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -951,7 +951,7 @@ with recursive search_graph(f, t, label) as (
select g.*
from graph g, search_graph sg
where g.f = sg.t
-) cycle f, t set is_cycle to true default false using path
+) cycle f, t set is_cycle using path
select * from search_graph;
f | t | label | is_cycle | path
---+---+------------+----------+-------------------------------------------
@@ -1071,7 +1071,7 @@ with recursive a as (
select 1 as b
union all
select * from a
-) cycle b set c to true default false using p
+) cycle b set c using p
select * from a;
b | c | p
---+---+-----------
@@ -1087,7 +1087,7 @@ with recursive search_graph(f, t, label) as (
from graph g, search_graph sg
where g.f = sg.t
) search depth first by f, t set seq
- cycle f, t set is_cycle to true default false using path
+ cycle f, t set is_cycle using path
select * from search_graph;
f | t | label | seq | is_cycle | path
---+---+------------+-------------------------------------------+----------+-------------------------------------------
@@ -1125,7 +1125,7 @@ with recursive search_graph(f, t, label) as (
from graph g, search_graph sg
where g.f = sg.t
) search breadth first by f, t set seq
- cycle f, t set is_cycle to true default false using path
+ cycle f, t set is_cycle using path
select * from search_graph;
f | t | label | seq | is_cycle | path
---+---+------------+---------+----------+-------------------------------------------
@@ -1163,10 +1163,10 @@ with recursive search_graph(f, t, label) as (
select g.*
from graph g, search_graph sg
where g.f = sg.t
-) cycle foo, tar set is_cycle to true default false using path
+) cycle foo, tar set is_cycle using path
select * from search_graph;
ERROR: cycle column "foo" not in WITH query column list
-LINE 7: ) cycle foo, tar set is_cycle to true default false using pa...
+LINE 7: ) cycle foo, tar set is_cycle using path
^
with recursive search_graph(f, t, label) as (
select * from graph g
@@ -1257,38 +1257,99 @@ ERROR: search_sequence column name and cycle path column name are the same
LINE 7: ) search depth first by f, t set foo
^
-- test ruleutils and view expansion
-create temp view v_cycle as
+create temp view v_cycle1 as
with recursive search_graph(f, t, label) as (
select * from graph g
union all
select g.*
from graph g, search_graph sg
where g.f = sg.t
-) cycle f, t set is_cycle to true default false using path
+) cycle f, t set is_cycle using path
select f, t, label from search_graph;
-select pg_get_viewdef('v_cycle');
- pg_get_viewdef
---------------------------------------------------------------------
- WITH RECURSIVE search_graph(f, t, label) AS ( +
- SELECT g.f, +
- g.t, +
- g.label +
- FROM graph g +
- UNION ALL +
- SELECT g.f, +
- g.t, +
- g.label +
- FROM graph g, +
- search_graph sg +
- WHERE (g.f = sg.t) +
- ) CYCLE f, t SET is_cycle TO true DEFAULT false USING path+
- SELECT search_graph.f, +
- search_graph.t, +
- search_graph.label +
+create temp view v_cycle2 as
+with recursive search_graph(f, t, label) as (
+ select * from graph g
+ union all
+ select g.*
+ from graph g, search_graph sg
+ where g.f = sg.t
+) cycle f, t set is_cycle to 'Y' default 'N' using path
+select f, t, label from search_graph;
+select pg_get_viewdef('v_cycle1');
+ pg_get_viewdef
+------------------------------------------------
+ WITH RECURSIVE search_graph(f, t, label) AS (+
+ SELECT g.f, +
+ g.t, +
+ g.label +
+ FROM graph g +
+ UNION ALL +
+ SELECT g.f, +
+ g.t, +
+ g.label +
+ FROM graph g, +
+ search_graph sg +
+ WHERE (g.f = sg.t) +
+ ) CYCLE f, t SET is_cycle USING path +
+ SELECT search_graph.f, +
+ search_graph.t, +
+ search_graph.label +
FROM search_graph;
(1 row)
-select * from v_cycle;
+select pg_get_viewdef('v_cycle2');
+ pg_get_viewdef
+-----------------------------------------------------------------------------
+ WITH RECURSIVE search_graph(f, t, label) AS ( +
+ SELECT g.f, +
+ g.t, +
+ g.label +
+ FROM graph g +
+ UNION ALL +
+ SELECT g.f, +
+ g.t, +
+ g.label +
+ FROM graph g, +
+ search_graph sg +
+ WHERE (g.f = sg.t) +
+ ) CYCLE f, t SET is_cycle TO 'Y'::text DEFAULT 'N'::text USING path+
+ SELECT search_graph.f, +
+ search_graph.t, +
+ search_graph.label +
+ FROM search_graph;
+(1 row)
+
+select * from v_cycle1;
+ f | t | label
+---+---+------------
+ 1 | 2 | arc 1 -> 2
+ 1 | 3 | arc 1 -> 3
+ 2 | 3 | arc 2 -> 3
+ 1 | 4 | arc 1 -> 4
+ 4 | 5 | arc 4 -> 5
+ 5 | 1 | arc 5 -> 1
+ 1 | 2 | arc 1 -> 2
+ 1 | 3 | arc 1 -> 3
+ 1 | 4 | arc 1 -> 4
+ 2 | 3 | arc 2 -> 3
+ 4 | 5 | arc 4 -> 5
+ 5 | 1 | arc 5 -> 1
+ 1 | 2 | arc 1 -> 2
+ 1 | 3 | arc 1 -> 3
+ 1 | 4 | arc 1 -> 4
+ 2 | 3 | arc 2 -> 3
+ 4 | 5 | arc 4 -> 5
+ 5 | 1 | arc 5 -> 1
+ 1 | 2 | arc 1 -> 2
+ 1 | 3 | arc 1 -> 3
+ 1 | 4 | arc 1 -> 4
+ 2 | 3 | arc 2 -> 3
+ 4 | 5 | arc 4 -> 5
+ 5 | 1 | arc 5 -> 1
+ 2 | 3 | arc 2 -> 3
+(25 rows)
+
+select * from v_cycle2;
f | t | label
---+---+------------
1 | 2 | arc 1 -> 2
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index 85a671c6300..1a9bdc9f3ea 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -509,7 +509,7 @@ with recursive search_graph(f, t, label) as (
select g.*
from graph g, search_graph sg
where g.f = sg.t
-) cycle f, t set is_cycle to true default false using path
+) cycle f, t set is_cycle using path
select * from search_graph;
with recursive search_graph(f, t, label) as (
@@ -545,7 +545,7 @@ with recursive a as (
select 1 as b
union all
select * from a
-) cycle b set c to true default false using p
+) cycle b set c using p
select * from a;
-- search+cycle
@@ -556,7 +556,7 @@ with recursive search_graph(f, t, label) as (
from graph g, search_graph sg
where g.f = sg.t
) search depth first by f, t set seq
- cycle f, t set is_cycle to true default false using path
+ cycle f, t set is_cycle using path
select * from search_graph;
with recursive search_graph(f, t, label) as (
@@ -566,7 +566,7 @@ with recursive search_graph(f, t, label) as (
from graph g, search_graph sg
where g.f = sg.t
) search breadth first by f, t set seq
- cycle f, t set is_cycle to true default false using path
+ cycle f, t set is_cycle using path
select * from search_graph;
-- various syntax errors
@@ -576,7 +576,7 @@ with recursive search_graph(f, t, label) as (
select g.*
from graph g, search_graph sg
where g.f = sg.t
-) cycle foo, tar set is_cycle to true default false using path
+) cycle foo, tar set is_cycle using path
select * from search_graph;
with recursive search_graph(f, t, label) as (
@@ -654,19 +654,31 @@ with recursive search_graph(f, t, label) as (
select * from search_graph;
-- test ruleutils and view expansion
-create temp view v_cycle as
+create temp view v_cycle1 as
with recursive search_graph(f, t, label) as (
select * from graph g
union all
select g.*
from graph g, search_graph sg
where g.f = sg.t
-) cycle f, t set is_cycle to true default false using path
+) cycle f, t set is_cycle using path
+select f, t, label from search_graph;
+
+create temp view v_cycle2 as
+with recursive search_graph(f, t, label) as (
+ select * from graph g
+ union all
+ select g.*
+ from graph g, search_graph sg
+ where g.f = sg.t
+) cycle f, t set is_cycle to 'Y' default 'N' using path
select f, t, label from search_graph;
-select pg_get_viewdef('v_cycle');
+select pg_get_viewdef('v_cycle1');
+select pg_get_viewdef('v_cycle2');
-select * from v_cycle;
+select * from v_cycle1;
+select * from v_cycle2;
--
-- test multiple WITH queries
generated by cgit v1.2.3 (git 2.25.1) at 2025-06-01 17:20:21 +0000