public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tatsuo Ishii <[email protected]>
To: [email protected]
To: [email protected]
To: [email protected]
To: [email protected]
To: [email protected]
Cc: [email protected]
Subject: Re: Row pattern recognition
Date: Wed, 24 Sep 2025 19:35:23 +0900 (JST)
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
Attached are the v33 patches for Row pattern recognition. The
difference from v32 is that the raw parse tree printing patch is not
included in v33. This is because now that we have
debug_print_raw_parse.
Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
Attachments:
[application/octet-stream] v33-0001-Row-pattern-recognition-patch-for-raw-parser.patch (15.2K, ../[email protected]/2-v33-0001-Row-pattern-recognition-patch-for-raw-parser.patch)
download | inline diff:
From d4c4d6c77a5899de41946d0ca7d46dd035cf26aa Mon Sep 17 00:00:00 2001
From: Tatsuo Ishii <[email protected]>
Date: Wed, 24 Sep 2025 19:18:37 +0900
Subject: [PATCH v33 1/8] Row pattern recognition patch for raw parser.
---
src/backend/parser/gram.y | 122 +++++++++++++++++++++++++++-----
src/include/nodes/parsenodes.h | 47 ++++++++++++
src/include/parser/kwlist.h | 5 ++
src/include/parser/parse_node.h | 1 +
4 files changed, 159 insertions(+), 16 deletions(-)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 9fd48acb1f8..e9ad9824fe4 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -671,6 +671,13 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
json_object_constructor_null_clause_opt
json_array_constructor_null_clause_opt
+%type <target> row_pattern_definition
+%type <node> opt_row_pattern_common_syntax
+ row_pattern_term
+%type <list> row_pattern_definition_list
+ row_pattern
+%type <ival> opt_row_pattern_skip_to
+%type <boolean> opt_row_pattern_initial_or_seek
/*
* Non-keyword token types. These are hard-wired into the "flex" lexer.
@@ -714,7 +721,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
DATA_P DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
- DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DEPENDS DEPTH DESC
+ DEFERRABLE DEFERRED DEFINE DEFINER DELETE_P DELIMITER DELIMITERS DEPENDS DEPTH DESC
DETACH DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P
DOUBLE_P DROP
@@ -730,7 +737,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
HANDLER HAVING HEADER_P HOLD HOUR_P
IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P INCLUDE
- INCLUDING INCREMENT INDENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P
+ INCLUDING INCREMENT INDENT INDEX INDEXES INHERIT INHERITS INITIAL INITIALLY INLINE_P
INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER
INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION
@@ -755,8 +762,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
ORDER ORDINALITY OTHERS OUT_P OUTER_P
OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER
- PARALLEL PARAMETER PARSER PARTIAL PARTITION PASSING PASSWORD PATH
- PERIOD PLACING PLAN PLANS POLICY
+ PARALLEL PARAMETER PARSER PARTIAL PARTITION PASSING PASSWORD PAST
+ PATH PATTERN_P PERIOD PLACING PLAN PLANS POLICY
POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION
@@ -767,7 +774,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
RESET RESTART RESTRICT RETURN RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP
ROUTINE ROUTINES ROW ROWS RULE
- SAVEPOINT SCALAR SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT
+ SAVEPOINT SCALAR SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SEEK SELECT
SEQUENCE SEQUENCES
SERIALIZABLE SERVER SESSION SESSION_USER SET SETS SETOF SHARE SHOW
SIMILAR SIMPLE SKIP SMALLINT SNAPSHOT SOME SOURCE SQL_P STABLE STANDALONE_P
@@ -850,8 +857,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
* reference point for a precedence level that we can assign to other
* keywords that lack a natural precedence level.
*
- * We need to do this for PARTITION, RANGE, ROWS, and GROUPS to support
- * opt_existing_window_name (see comment there).
+ * We need to do this for PARTITION, RANGE, ROWS, GROUPS, AFTER, INITIAL,
+ * SEEK, PATTERN_P to support opt_existing_window_name (see comment there).
*
* The frame_bound productions UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
* are even messier: since UNBOUNDED is an unreserved keyword (per spec!),
@@ -881,6 +888,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%nonassoc UNBOUNDED NESTED /* ideally would have same precedence as IDENT */
%nonassoc IDENT PARTITION RANGE ROWS GROUPS PRECEDING FOLLOWING CUBE ROLLUP
SET KEYS OBJECT_P SCALAR VALUE_P WITH WITHOUT PATH
+ AFTER INITIAL SEEK PATTERN_P
%left Op OPERATOR /* multi-character ops and user-defined operators */
%left '+' '-'
%left '*' '/' '%'
@@ -16463,7 +16471,8 @@ over_clause: OVER window_specification
;
window_specification: '(' opt_existing_window_name opt_partition_clause
- opt_sort_clause opt_frame_clause ')'
+ opt_sort_clause opt_frame_clause
+ opt_row_pattern_common_syntax ')'
{
WindowDef *n = makeNode(WindowDef);
@@ -16475,20 +16484,21 @@ window_specification: '(' opt_existing_window_name opt_partition_clause
n->frameOptions = $5->frameOptions;
n->startOffset = $5->startOffset;
n->endOffset = $5->endOffset;
+ n->rpCommonSyntax = (RPCommonSyntax *)$6;
n->location = @1;
$$ = n;
}
;
/*
- * If we see PARTITION, RANGE, ROWS or GROUPS as the first token after the '('
- * of a window_specification, we want the assumption to be that there is
- * no existing_window_name; but those keywords are unreserved and so could
- * be ColIds. We fix this by making them have the same precedence as IDENT
- * and giving the empty production here a slightly higher precedence, so
- * that the shift/reduce conflict is resolved in favor of reducing the rule.
- * These keywords are thus precluded from being an existing_window_name but
- * are not reserved for any other purpose.
+ * If we see PARTITION, RANGE, ROWS, GROUPS, AFTER, INITIAL, SEEK or PATTERN_P
+ * as the first token after the '(' of a window_specification, we want the
+ * assumption to be that there is no existing_window_name; but those keywords
+ * are unreserved and so could be ColIds. We fix this by making them have the
+ * same precedence as IDENT and giving the empty production here a slightly
+ * higher precedence, so that the shift/reduce conflict is resolved in favor
+ * of reducing the rule. These keywords are thus precluded from being an
+ * existing_window_name but are not reserved for any other purpose.
*/
opt_existing_window_name: ColId { $$ = $1; }
| /*EMPTY*/ %prec Op { $$ = NULL; }
@@ -16657,6 +16667,76 @@ opt_window_exclusion_clause:
| /*EMPTY*/ { $$ = 0; }
;
+opt_row_pattern_common_syntax:
+opt_row_pattern_skip_to opt_row_pattern_initial_or_seek
+ PATTERN_P '(' row_pattern ')'
+ DEFINE row_pattern_definition_list
+ {
+ RPCommonSyntax *n = makeNode(RPCommonSyntax);
+ n->rpSkipTo = $1;
+ n->initial = $2;
+ n->rpPatterns = $5;
+ n->rpDefs = $8;
+ $$ = (Node *) n;
+ }
+ | /*EMPTY*/ { $$ = NULL; }
+ ;
+
+opt_row_pattern_skip_to:
+ AFTER MATCH SKIP TO NEXT ROW
+ {
+ $$ = ST_NEXT_ROW;
+ }
+ | AFTER MATCH SKIP PAST LAST_P ROW
+ {
+ $$ = ST_PAST_LAST_ROW;
+ }
+ | /*EMPTY*/
+ {
+ $$ = ST_PAST_LAST_ROW;
+ }
+ ;
+
+opt_row_pattern_initial_or_seek:
+ INITIAL { $$ = true; }
+ | SEEK
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("SEEK is not supported"),
+ errhint("Use INITIAL instead."),
+ parser_errposition(@1)));
+ }
+ | /*EMPTY*/ { $$ = true; }
+ ;
+
+row_pattern:
+ row_pattern_term { $$ = list_make1($1); }
+ | row_pattern row_pattern_term { $$ = lappend($1, $2); }
+ ;
+
+row_pattern_term:
+ ColId { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "", (Node *)makeString($1), NULL, @1); }
+ | ColId '*' { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "*", (Node *)makeString($1), NULL, @1); }
+ | ColId '+' { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "+", (Node *)makeString($1), NULL, @1); }
+ | ColId '?' { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "?", (Node *)makeString($1), NULL, @1); }
+ ;
+
+row_pattern_definition_list:
+ row_pattern_definition { $$ = list_make1($1); }
+ | row_pattern_definition_list ',' row_pattern_definition { $$ = lappend($1, $3); }
+ ;
+
+row_pattern_definition:
+ ColId AS a_expr
+ {
+ $$ = makeNode(ResTarget);
+ $$->name = $1;
+ $$->indirection = NIL;
+ $$->val = (Node *) $3;
+ $$->location = @1;
+ }
+ ;
/*
* Supporting nonterminals for expressions.
@@ -17859,6 +17939,7 @@ unreserved_keyword:
| INDEXES
| INHERIT
| INHERITS
+ | INITIAL
| INLINE_P
| INPUT_P
| INSENSITIVE
@@ -17932,7 +18013,9 @@ unreserved_keyword:
| PARTITION
| PASSING
| PASSWORD
+ | PAST
| PATH
+ | PATTERN_P
| PERIOD
| PLAN
| PLANS
@@ -17985,6 +18068,7 @@ unreserved_keyword:
| SEARCH
| SECOND_P
| SECURITY
+ | SEEK
| SEQUENCE
| SEQUENCES
| SERIALIZABLE
@@ -18207,6 +18291,7 @@ reserved_keyword:
| CURRENT_USER
| DEFAULT
| DEFERRABLE
+ | DEFINE
| DESC
| DISTINCT
| DO
@@ -18370,6 +18455,7 @@ bare_label_keyword:
| DEFAULTS
| DEFERRABLE
| DEFERRED
+ | DEFINE
| DEFINER
| DELETE_P
| DELIMITER
@@ -18448,6 +18534,7 @@ bare_label_keyword:
| INDEXES
| INHERIT
| INHERITS
+ | INITIAL
| INITIALLY
| INLINE_P
| INNER_P
@@ -18559,7 +18646,9 @@ bare_label_keyword:
| PARTITION
| PASSING
| PASSWORD
+ | PAST
| PATH
+ | PATTERN_P
| PERIOD
| PLACING
| PLAN
@@ -18618,6 +18707,7 @@ bare_label_keyword:
| SCROLL
| SEARCH
| SECURITY
+ | SEEK
| SELECT
| SEQUENCE
| SEQUENCES
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 4ed14fc5b78..f0485353405 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -576,6 +576,31 @@ typedef struct SortBy
ParseLoc location; /* operator location, or -1 if none/unknown */
} SortBy;
+/*
+ * AFTER MATCH row pattern skip to types in row pattern common syntax
+ */
+typedef enum RPSkipTo
+{
+ ST_NONE, /* AFTER MATCH omitted */
+ ST_NEXT_ROW, /* SKIP TO NEXT ROW */
+ ST_PAST_LAST_ROW, /* SKIP TO PAST LAST ROW */
+} RPSkipTo;
+
+/*
+ * RowPatternCommonSyntax - raw representation of row pattern common syntax
+ *
+ */
+typedef struct RPCommonSyntax
+{
+ NodeTag type;
+ RPSkipTo rpSkipTo; /* Row Pattern AFTER MATCH SKIP type */
+ bool initial; /* true if <row pattern initial or seek> is
+ * initial */
+ List *rpPatterns; /* PATTERN variables (list of A_Expr) */
+ List *rpDefs; /* row pattern definitions clause (list of
+ * ResTarget) */
+} RPCommonSyntax;
+
/*
* WindowDef - raw representation of WINDOW and OVER clauses
*
@@ -591,6 +616,7 @@ typedef struct WindowDef
char *refname; /* referenced window name, if any */
List *partitionClause; /* PARTITION BY expression list */
List *orderClause; /* ORDER BY (list of SortBy) */
+ RPCommonSyntax *rpCommonSyntax; /* row pattern common syntax */
int frameOptions; /* frame_clause options, see below */
Node *startOffset; /* expression for starting bound, if any */
Node *endOffset; /* expression for ending bound, if any */
@@ -1559,6 +1585,11 @@ typedef struct GroupingSet
* the orderClause might or might not be copied (see copiedOrder); the framing
* options are never copied, per spec.
*
+ * "defineClause" is Row Pattern Recognition DEFINE clause (list of
+ * TargetEntry). TargetEntry.resname represents row pattern definition
+ * variable name. "patternVariable" and "patternRegexp" represents PATTERN
+ * clause.
+ *
* The information relevant for the query jumbling is the partition clause
* type and its bounds.
*/
@@ -1588,6 +1619,22 @@ typedef struct WindowClause
Index winref; /* ID referenced by window functions */
/* did we copy orderClause from refname? */
bool copiedOrder pg_node_attr(query_jumble_ignore);
+ /* Row Pattern AFTER MACH SKIP clause */
+ RPSkipTo rpSkipTo; /* Row Pattern Skip To type */
+ bool initial; /* true if <row pattern initial or seek> is
+ * initial */
+ /* Row Pattern DEFINE clause (list of TargetEntry) */
+ List *defineClause;
+ /* Row Pattern DEFINE variable initial names (list of String) */
+ List *defineInitial;
+ /* Row Pattern PATTERN variable name (list of String) */
+ List *patternVariable;
+
+ /*
+ * Row Pattern PATTERN regular expression quantifier ('+' or ''. list of
+ * String)
+ */
+ List *patternRegexp;
} WindowClause;
/*
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index a4af3f717a1..68911d8e835 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -129,6 +129,7 @@ PG_KEYWORD("default", DEFAULT, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("defaults", DEFAULTS, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("deferrable", DEFERRABLE, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("deferred", DEFERRED, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("define", DEFINE, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("definer", DEFINER, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("delete", DELETE_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("delimiter", DELIMITER, UNRESERVED_KEYWORD, BARE_LABEL)
@@ -216,6 +217,7 @@ PG_KEYWORD("index", INDEX, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("indexes", INDEXES, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("inherit", INHERIT, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("inherits", INHERITS, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("initial", INITIAL, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("initially", INITIALLY, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("inline", INLINE_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("inner", INNER_P, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
@@ -339,7 +341,9 @@ PG_KEYWORD("partial", PARTIAL, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("partition", PARTITION, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("passing", PASSING, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("password", PASSWORD, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("past", PAST, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("path", PATH, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("pattern", PATTERN_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("period", PERIOD, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("plan", PLAN, UNRESERVED_KEYWORD, BARE_LABEL)
@@ -401,6 +405,7 @@ PG_KEYWORD("scroll", SCROLL, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("search", SEARCH, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("second", SECOND_P, UNRESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("security", SECURITY, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("seek", SEEK, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("select", SELECT, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("sequence", SEQUENCE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("sequences", SEQUENCES, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index f7d07c84542..d286a8b7783 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -51,6 +51,7 @@ typedef enum ParseExprKind
EXPR_KIND_WINDOW_FRAME_RANGE, /* window frame clause with RANGE */
EXPR_KIND_WINDOW_FRAME_ROWS, /* window frame clause with ROWS */
EXPR_KIND_WINDOW_FRAME_GROUPS, /* window frame clause with GROUPS */
+ EXPR_KIND_RPR_DEFINE, /* DEFINE */
EXPR_KIND_SELECT_TARGET, /* SELECT target list item */
EXPR_KIND_INSERT_TARGET, /* INSERT target list item */
EXPR_KIND_UPDATE_SOURCE, /* UPDATE assignment source item */
--
2.43.0
[application/octet-stream] v33-0002-Row-pattern-recognition-patch-parse-analysis.patch (10.8K, ../[email protected]/3-v33-0002-Row-pattern-recognition-patch-parse-analysis.patch)
download | inline diff:
From ad278020cc761a8a7b2910f0cf514822e6b73e71 Mon Sep 17 00:00:00 2001
From: Tatsuo Ishii <[email protected]>
Date: Wed, 24 Sep 2025 19:18:37 +0900
Subject: [PATCH v33 2/8] Row pattern recognition patch (parse/analysis).
---
src/backend/parser/parse_agg.c | 7 +
src/backend/parser/parse_clause.c | 270 +++++++++++++++++++++++++++++-
src/backend/parser/parse_expr.c | 6 +
src/backend/parser/parse_func.c | 3 +
4 files changed, 285 insertions(+), 1 deletion(-)
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 3254c83cc6c..e820adf9267 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -580,6 +580,10 @@ check_agglevels_and_constraints(ParseState *pstate, Node *expr)
errkind = true;
break;
+ case EXPR_KIND_RPR_DEFINE:
+ errkind = true;
+ break;
+
/*
* There is intentionally no default: case here, so that the
* compiler will warn if we add a new ParseExprKind without
@@ -996,6 +1000,9 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc,
case EXPR_KIND_CYCLE_MARK:
errkind = true;
break;
+ case EXPR_KIND_RPR_DEFINE:
+ errkind = true;
+ break;
/*
* There is intentionally no default: case here, so that the
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 9f20a70ce13..a94fe5f3f57 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -96,7 +96,12 @@ static WindowClause *findWindowClause(List *wclist, const char *name);
static Node *transformFrameOffset(ParseState *pstate, int frameOptions,
Oid rangeopfamily, Oid rangeopcintype, Oid *inRangeFunc,
Node *clause);
-
+static void transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef,
+ List **targetlist);
+static List *transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef,
+ List **targetlist);
+static void transformPatternClause(ParseState *pstate, WindowClause *wc,
+ WindowDef *windef);
/*
* transformFromClause -
@@ -2956,6 +2961,10 @@ transformWindowDefinitions(ParseState *pstate,
rangeopfamily, rangeopcintype,
&wc->endInRangeFunc,
windef->endOffset);
+
+ /* Process Row Pattern Recognition related clauses */
+ transformRPR(pstate, wc, windef, targetlist);
+
wc->winref = winref;
result = lappend(result, wc);
@@ -3823,3 +3832,262 @@ transformFrameOffset(ParseState *pstate, int frameOptions,
return node;
}
+
+/*
+ * transformRPR
+ * Process Row Pattern Recognition related clauses
+ */
+static void
+transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef,
+ List **targetlist)
+{
+ /*
+ * Window definition exists?
+ */
+ if (windef == NULL)
+ return;
+
+ /*
+ * Row Pattern Common Syntax clause exists?
+ */
+ if (windef->rpCommonSyntax == NULL)
+ return;
+
+ /* Check Frame option. Frame must start at current row */
+ if ((wc->frameOptions & FRAMEOPTION_START_CURRENT_ROW) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("FRAME must start at current row when row patttern recognition is used")));
+
+ /* Transform AFTER MACH SKIP TO clause */
+ wc->rpSkipTo = windef->rpCommonSyntax->rpSkipTo;
+
+ /* Transform SEEK or INITIAL clause */
+ wc->initial = windef->rpCommonSyntax->initial;
+
+ /* Transform DEFINE clause into list of TargetEntry's */
+ wc->defineClause = transformDefineClause(pstate, wc, windef, targetlist);
+
+ /* Check PATTERN clause and copy to patternClause */
+ transformPatternClause(pstate, wc, windef);
+}
+
+/*
+ * transformDefineClause
+ * Process DEFINE clause and transform ResTarget into list of
+ * TargetEntry.
+ *
+ * XXX we only support column reference in row pattern definition search
+ * condition, e.g. "price". <row pattern definition variable name>.<column
+ * reference> is not supported, e.g. "A.price".
+ */
+static List *
+transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef,
+ List **targetlist)
+{
+ /* DEFINE variable name initials */
+ static char *defineVariableInitials = "abcdefghijklmnopqrstuvwxyz";
+
+ ListCell *lc,
+ *l;
+ ResTarget *restarget,
+ *r;
+ List *restargets;
+ List *defineClause;
+ char *name;
+ int initialLen;
+ int i;
+
+ /*
+ * If Row Definition Common Syntax exists, DEFINE clause must exist. (the
+ * raw parser should have already checked it.)
+ */
+ Assert(windef->rpCommonSyntax->rpDefs != NULL);
+
+ /*
+ * Check and add "A AS A IS TRUE" if pattern variable is missing in DEFINE
+ * per the SQL standard.
+ */
+ restargets = NIL;
+ foreach(lc, windef->rpCommonSyntax->rpPatterns)
+ {
+ A_Expr *a;
+ bool found = false;
+
+ if (!IsA(lfirst(lc), A_Expr))
+ ereport(ERROR,
+ errmsg("node type is not A_Expr"));
+
+ a = (A_Expr *) lfirst(lc);
+ name = strVal(a->lexpr);
+
+ foreach(l, windef->rpCommonSyntax->rpDefs)
+ {
+ restarget = (ResTarget *) lfirst(l);
+
+ if (!strcmp(restarget->name, name))
+ {
+ found = true;
+ break;
+ }
+ }
+
+ if (!found)
+ {
+ /*
+ * "name" is missing. So create "name AS name IS TRUE" ResTarget
+ * node and add it to the temporary list.
+ */
+ A_Const *n;
+
+ restarget = makeNode(ResTarget);
+ n = makeNode(A_Const);
+ n->val.boolval.type = T_Boolean;
+ n->val.boolval.boolval = true;
+ n->location = -1;
+ restarget->name = pstrdup(name);
+ restarget->indirection = NIL;
+ restarget->val = (Node *) n;
+ restarget->location = -1;
+ restargets = lappend((List *) restargets, restarget);
+ }
+ }
+
+ if (list_length(restargets) >= 1)
+ {
+ /* add missing DEFINEs */
+ windef->rpCommonSyntax->rpDefs =
+ list_concat(windef->rpCommonSyntax->rpDefs, restargets);
+ list_free(restargets);
+ }
+
+ /*
+ * Check for duplicate row pattern definition variables. The standard
+ * requires that no two row pattern definition variable names shall be
+ * equivalent.
+ */
+ restargets = NIL;
+ foreach(lc, windef->rpCommonSyntax->rpDefs)
+ {
+ restarget = (ResTarget *) lfirst(lc);
+ name = restarget->name;
+
+ /*
+ * Add DEFINE expression (Restarget->val) to the targetlist as a
+ * TargetEntry if it does not exist yet. Planner will add the column
+ * ref var node to the outer plan's target list later on. This makes
+ * DEFINE expression could access the outer tuple while evaluating
+ * PATTERN.
+ *
+ * XXX: adding whole expressions of DEFINE to the plan.targetlist is
+ * not so good, because it's not necessary to evalute the expression
+ * in the target list while running the plan. We should extract the
+ * var nodes only then add them to the plan.targetlist.
+ */
+ findTargetlistEntrySQL99(pstate, (Node *) restarget->val,
+ targetlist, EXPR_KIND_RPR_DEFINE);
+
+ /*
+ * Make sure that the row pattern definition search condition is a
+ * boolean expression.
+ */
+ transformWhereClause(pstate, restarget->val,
+ EXPR_KIND_RPR_DEFINE, "DEFINE");
+
+ foreach(l, restargets)
+ {
+ char *n;
+
+ r = (ResTarget *) lfirst(l);
+ n = r->name;
+
+ if (!strcmp(n, name))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("row pattern definition variable name \"%s\" appears more than once in DEFINE clause",
+ name),
+ parser_errposition(pstate, exprLocation((Node *) r))));
+ }
+ restargets = lappend(restargets, restarget);
+ }
+ list_free(restargets);
+
+ /*
+ * Create list of row pattern DEFINE variable name's initial. We assign
+ * [a-z] to them (up to 26 variable names are allowed).
+ */
+ restargets = NIL;
+ i = 0;
+ initialLen = strlen(defineVariableInitials);
+
+ foreach(lc, windef->rpCommonSyntax->rpDefs)
+ {
+ char initial[2];
+
+ restarget = (ResTarget *) lfirst(lc);
+ name = restarget->name;
+
+ if (i >= initialLen)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("number of row pattern definition variable names exceeds %d",
+ initialLen),
+ parser_errposition(pstate,
+ exprLocation((Node *) restarget))));
+ }
+ initial[0] = defineVariableInitials[i++];
+ initial[1] = '\0';
+ wc->defineInitial = lappend(wc->defineInitial,
+ makeString(pstrdup(initial)));
+ }
+
+ defineClause = transformTargetList(pstate, windef->rpCommonSyntax->rpDefs,
+ EXPR_KIND_RPR_DEFINE);
+
+ /* mark column origins */
+ markTargetListOrigins(pstate, defineClause);
+
+ /* mark all nodes in the DEFINE clause tree with collation information */
+ assign_expr_collations(pstate, (Node *) defineClause);
+
+ return defineClause;
+}
+
+/*
+ * transformPatternClause
+ * Process PATTERN clause and return PATTERN clause in the raw parse tree
+ */
+static void
+transformPatternClause(ParseState *pstate, WindowClause *wc,
+ WindowDef *windef)
+{
+ ListCell *lc;
+
+ /*
+ * Row Pattern Common Syntax clause exists?
+ */
+ if (windef->rpCommonSyntax == NULL)
+ return;
+
+ wc->patternVariable = NIL;
+ wc->patternRegexp = NIL;
+ foreach(lc, windef->rpCommonSyntax->rpPatterns)
+ {
+ A_Expr *a;
+ char *name;
+ char *regexp;
+
+ if (!IsA(lfirst(lc), A_Expr))
+ ereport(ERROR,
+ errmsg("node type is not A_Expr"));
+
+ a = (A_Expr *) lfirst(lc);
+ name = strVal(a->lexpr);
+
+ wc->patternVariable = lappend(wc->patternVariable, makeString(pstrdup(name)));
+ regexp = strVal(lfirst(list_head(a->name)));
+
+ wc->patternRegexp = lappend(wc->patternRegexp, makeString(pstrdup(regexp)));
+ }
+}
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index e1979a80c19..6c4de20f1c0 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -574,6 +574,7 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
case EXPR_KIND_COPY_WHERE:
case EXPR_KIND_GENERATED_COLUMN:
case EXPR_KIND_CYCLE_MARK:
+ case EXPR_KIND_RPR_DEFINE:
/* okay */
break;
@@ -1859,6 +1860,9 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
case EXPR_KIND_GENERATED_COLUMN:
err = _("cannot use subquery in column generation expression");
break;
+ case EXPR_KIND_RPR_DEFINE:
+ err = _("cannot use subquery in DEFINE expression");
+ break;
/*
* There is intentionally no default: case here, so that the
@@ -3218,6 +3222,8 @@ ParseExprKindName(ParseExprKind exprKind)
return "GENERATED AS";
case EXPR_KIND_CYCLE_MARK:
return "CYCLE";
+ case EXPR_KIND_RPR_DEFINE:
+ return "DEFINE";
/*
* There is intentionally no default: case here, so that the
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index c43020a769d..b6ebe0fdfce 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -2774,6 +2774,9 @@ check_srf_call_placement(ParseState *pstate, Node *last_srf, int location)
case EXPR_KIND_CYCLE_MARK:
errkind = true;
break;
+ case EXPR_KIND_RPR_DEFINE:
+ errkind = true;
+ break;
/*
* There is intentionally no default: case here, so that the
--
2.43.0
[application/octet-stream] v33-0003-Row-pattern-recognition-patch-rewriter.patch (4.3K, ../[email protected]/4-v33-0003-Row-pattern-recognition-patch-rewriter.patch)
download | inline diff:
From 39cce3c3b077392353d1bf54f9d75b1e66c4e572 Mon Sep 17 00:00:00 2001
From: Tatsuo Ishii <[email protected]>
Date: Wed, 24 Sep 2025 19:18:37 +0900
Subject: [PATCH v33 3/8] Row pattern recognition patch (rewriter).
---
src/backend/utils/adt/ruleutils.c | 112 ++++++++++++++++++++++++++++++
1 file changed, 112 insertions(+)
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 0408a95941d..05fc4911531 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -438,6 +438,10 @@ static void get_rule_groupingset(GroupingSet *gset, List *targetlist,
bool omit_parens, deparse_context *context);
static void get_rule_orderby(List *orderList, List *targetList,
bool force_colno, deparse_context *context);
+static void get_rule_pattern(List *patternVariable, List *patternRegexp,
+ bool force_colno, deparse_context *context);
+static void get_rule_define(List *defineClause, List *patternVariables,
+ bool force_colno, deparse_context *context);
static void get_rule_windowclause(Query *query, deparse_context *context);
static void get_rule_windowspec(WindowClause *wc, List *targetList,
deparse_context *context);
@@ -6739,6 +6743,67 @@ get_rule_orderby(List *orderList, List *targetList,
}
}
+/*
+ * Display a PATTERN clause.
+ */
+static void
+get_rule_pattern(List *patternVariable, List *patternRegexp,
+ bool force_colno, deparse_context *context)
+{
+ StringInfo buf = context->buf;
+ const char *sep;
+ ListCell *lc_var,
+ *lc_reg = list_head(patternRegexp);
+
+ sep = "";
+ appendStringInfoChar(buf, '(');
+ foreach(lc_var, patternVariable)
+ {
+ char *variable = strVal((String *) lfirst(lc_var));
+ char *regexp = NULL;
+
+ if (lc_reg != NULL)
+ {
+ regexp = strVal((String *) lfirst(lc_reg));
+
+ lc_reg = lnext(patternRegexp, lc_reg);
+ }
+
+ appendStringInfo(buf, "%s%s", sep, variable);
+ if (regexp !=NULL)
+ appendStringInfoString(buf, regexp);
+
+ sep = " ";
+ }
+ appendStringInfoChar(buf, ')');
+}
+
+/*
+ * Display a DEFINE clause.
+ */
+static void
+get_rule_define(List *defineClause, List *patternVariables,
+ bool force_colno, deparse_context *context)
+{
+ StringInfo buf = context->buf;
+ const char *sep;
+ ListCell *lc_var,
+ *lc_def;
+
+ sep = " ";
+ Assert(list_length(patternVariables) == list_length(defineClause));
+
+ forboth(lc_var, patternVariables, lc_def, defineClause)
+ {
+ char *varName = strVal(lfirst(lc_var));
+ TargetEntry *te = (TargetEntry *) lfirst(lc_def);
+
+ appendStringInfo(buf, "%s%s AS ", sep, varName);
+ get_rule_expr((Node *) te->expr, context, false);
+ sep = ",\n ";
+ }
+}
+
/*
* Display a WINDOW clause.
*
@@ -6819,6 +6884,7 @@ get_rule_windowspec(WindowClause *wc, List *targetList,
get_rule_orderby(wc->orderClause, targetList, false, context);
needspace = true;
}
+
/* framing clause is never inherited, so print unless it's default */
if (wc->frameOptions & FRAMEOPTION_NONDEFAULT)
{
@@ -6827,7 +6893,53 @@ get_rule_windowspec(WindowClause *wc, List *targetList,
get_window_frame_options(wc->frameOptions,
wc->startOffset, wc->endOffset,
context);
+ needspace = true;
}
+
+ /* RPR */
+ if (wc->rpSkipTo == ST_NEXT_ROW)
+ {
+ if (needspace)
+ appendStringInfoChar(buf, ' ');
+ appendStringInfoString(buf,
+ "\n AFTER MATCH SKIP TO NEXT ROW ");
+ needspace = true;
+ }
+ else if (wc->rpSkipTo == ST_PAST_LAST_ROW)
+ {
+ if (needspace)
+ appendStringInfoChar(buf, ' ');
+ appendStringInfoString(buf,
+ "\n AFTER MATCH SKIP PAST LAST ROW ");
+ needspace = true;
+ }
+ if (wc->initial)
+ {
+ if (needspace)
+ appendStringInfoChar(buf, ' ');
+ appendStringInfoString(buf, "\n INITIAL");
+ needspace = true;
+ }
+ if (wc->patternVariable)
+ {
+ if (needspace)
+ appendStringInfoChar(buf, ' ');
+ appendStringInfoString(buf, "\n PATTERN ");
+ get_rule_pattern(wc->patternVariable, wc->patternRegexp,
+ false, context);
+ needspace = true;
+ }
+
+ if (wc->defineClause)
+ {
+ if (needspace)
+ appendStringInfoChar(buf, ' ');
+ appendStringInfoString(buf, "\n DEFINE\n");
+ get_rule_define(wc->defineClause, wc->patternVariable,
+ false, context);
+ appendStringInfoChar(buf, ' ');
+ }
+
appendStringInfoChar(buf, ')');
}
--
2.43.0
[application/octet-stream] v33-0004-Row-pattern-recognition-patch-planner.patch (6.5K, ../[email protected]/5-v33-0004-Row-pattern-recognition-patch-planner.patch)
download | inline diff:
From cd38ef6a1c8225d0bd369417b70a6a73ad1ccbfc Mon Sep 17 00:00:00 2001
From: Tatsuo Ishii <[email protected]>
Date: Wed, 24 Sep 2025 19:18:37 +0900
Subject: [PATCH v33 4/8] Row pattern recognition patch (planner).
---
src/backend/optimizer/plan/createplan.c | 18 +++++++++++++--
src/backend/optimizer/plan/planner.c | 3 +++
src/backend/optimizer/plan/setrefs.c | 27 ++++++++++++++++++++++-
src/backend/optimizer/prep/prepjointree.c | 9 ++++++++
src/include/nodes/plannodes.h | 19 ++++++++++++++++
5 files changed, 73 insertions(+), 3 deletions(-)
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index c9dba7ff346..56178f75920 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -289,7 +289,9 @@ static Memoize *make_memoize(Plan *lefttree, Oid *hashoperators,
static WindowAgg *make_windowagg(List *tlist, WindowClause *wc,
int partNumCols, AttrNumber *partColIdx, Oid *partOperators, Oid *partCollations,
int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, Oid *ordCollations,
- List *runCondition, List *qual, bool topWindow,
+ List *runCondition, RPSkipTo rpSkipTo, List *patternVariable,
+ List *patternRegexp, List *defineClause, List *defineInitial,
+ List *qual, bool topWindow,
Plan *lefttree);
static Group *make_group(List *tlist, List *qual, int numGroupCols,
AttrNumber *grpColIdx, Oid *grpOperators, Oid *grpCollations,
@@ -2543,6 +2545,11 @@ create_windowagg_plan(PlannerInfo *root, WindowAggPath *best_path)
ordOperators,
ordCollations,
best_path->runCondition,
+ wc->rpSkipTo,
+ wc->patternVariable,
+ wc->patternRegexp,
+ wc->defineClause,
+ wc->defineInitial,
best_path->qual,
best_path->topwindow,
subplan);
@@ -6618,7 +6625,9 @@ static WindowAgg *
make_windowagg(List *tlist, WindowClause *wc,
int partNumCols, AttrNumber *partColIdx, Oid *partOperators, Oid *partCollations,
int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, Oid *ordCollations,
- List *runCondition, List *qual, bool topWindow, Plan *lefttree)
+ List *runCondition, RPSkipTo rpSkipTo, List *patternVariable,
+ List *patternRegexp, List *defineClause, List *defineInitial,
+ List *qual, bool topWindow, Plan *lefttree)
{
WindowAgg *node = makeNode(WindowAgg);
Plan *plan = &node->plan;
@@ -6645,6 +6654,11 @@ make_windowagg(List *tlist, WindowClause *wc,
node->inRangeAsc = wc->inRangeAsc;
node->inRangeNullsFirst = wc->inRangeNullsFirst;
node->topWindow = topWindow;
+ node->rpSkipTo = rpSkipTo;
+ node->patternVariable = patternVariable;
+ node->patternRegexp = patternRegexp;
+ node->defineClause = defineClause;
+ node->defineInitial = defineInitial;
plan->targetlist = tlist;
plan->lefttree = lefttree;
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 41bd8353430..1b008ba642c 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -929,6 +929,9 @@ subquery_planner(PlannerGlobal *glob, Query *parse, PlannerInfo *parent_root,
EXPRKIND_LIMIT);
wc->endOffset = preprocess_expression(root, wc->endOffset,
EXPRKIND_LIMIT);
+ wc->defineClause = (List *) preprocess_expression(root,
+ (Node *) wc->defineClause,
+ EXPRKIND_TARGET);
}
parse->limitOffset = preprocess_expression(root, parse->limitOffset,
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 6950eff2c5b..5b358c0fa41 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -211,7 +211,6 @@ static List *set_windowagg_runcondition_references(PlannerInfo *root,
List *runcondition,
Plan *plan);
-
/*****************************************************************************
*
* SUBPLAN REFERENCES
@@ -2557,6 +2556,32 @@ set_upper_references(PlannerInfo *root, Plan *plan, int rtoffset)
NRM_EQUAL,
NUM_EXEC_QUAL(plan));
+ /*
+ * Modifies an expression tree in each DEFINE clause so that all Var
+ * nodes's varno refers to OUTER_VAR.
+ */
+ if (IsA(plan, WindowAgg))
+ {
+ WindowAgg *wplan = (WindowAgg *) plan;
+
+ if (wplan->defineClause != NIL)
+ {
+ foreach(l, wplan->defineClause)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(l);
+
+ tle->expr = (Expr *)
+ fix_upper_expr(root,
+ (Node *) tle->expr,
+ subplan_itlist,
+ OUTER_VAR,
+ rtoffset,
+ NRM_EQUAL,
+ NUM_EXEC_QUAL(plan));
+ }
+ }
+ }
+
pfree(subplan_itlist);
}
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index 35e8d3c183b..7ef14d0d639 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -2506,6 +2506,15 @@ perform_pullup_replace_vars(PlannerInfo *root,
parse->returningList = (List *)
pullup_replace_vars((Node *) parse->returningList, rvcontext);
+ foreach(lc, parse->windowClause)
+ {
+ WindowClause *wc = lfirst_node(WindowClause, lc);
+
+ if (wc->defineClause != NIL)
+ wc->defineClause = (List *)
+ pullup_replace_vars((Node *) wc->defineClause, rvcontext);
+ }
+
if (parse->onConflict)
{
parse->onConflict->onConflictSet = (List *)
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 3d196f5078e..ec035d24915 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -20,6 +20,7 @@
#include "lib/stringinfo.h"
#include "nodes/bitmapset.h"
#include "nodes/lockoptions.h"
+#include "nodes/parsenodes.h"
#include "nodes/primnodes.h"
@@ -1286,6 +1287,24 @@ typedef struct WindowAgg
/* nulls sort first for in_range tests? */
bool inRangeNullsFirst;
+ /* Row Pattern Recognition AFTER MACH SKIP clause */
+ RPSkipTo rpSkipTo; /* Row Pattern Skip To type */
+
+ /* Row Pattern PATTERN variable name (list of String) */
+ List *patternVariable;
+
+ /*
+ * Row Pattern RPATTERN regular expression quantifier ('+' or ''. list of
+ * String)
+ */
+ List *patternRegexp;
+
+ /* Row Pattern DEFINE clause (list of TargetEntry) */
+ List *defineClause;
+
+ /* Row Pattern DEFINE variable initial names (list of String) */
+ List *defineInitial;
+
/*
* false for all apart from the WindowAgg that's closest to the root of
* the plan
--
2.43.0
[application/octet-stream] v33-0005-Row-pattern-recognition-patch-executor.patch (59.8K, ../[email protected]/6-v33-0005-Row-pattern-recognition-patch-executor.patch)
download | inline diff:
From db97db61b4d8aa4acf76906b4f466ecaf095217c Mon Sep 17 00:00:00 2001
From: Tatsuo Ishii <[email protected]>
Date: Wed, 24 Sep 2025 19:18:37 +0900
Subject: [PATCH v33 5/8] Row pattern recognition patch (executor).
---
src/backend/executor/nodeWindowAgg.c | 1835 +++++++++++++++++++++++++-
src/backend/utils/adt/windowfuncs.c | 34 +-
src/include/catalog/pg_proc.dat | 6 +
src/include/nodes/execnodes.h | 58 +
4 files changed, 1922 insertions(+), 11 deletions(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 9a1acce2b5d..00d4c5c3910 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -36,6 +36,7 @@
#include "access/htup_details.h"
#include "catalog/objectaccess.h"
#include "catalog/pg_aggregate.h"
+#include "catalog/pg_collation_d.h"
#include "catalog/pg_proc.h"
#include "executor/executor.h"
#include "executor/nodeWindowAgg.h"
@@ -45,9 +46,11 @@
#include "optimizer/optimizer.h"
#include "parser/parse_agg.h"
#include "parser/parse_coerce.h"
+#include "regex/regex.h"
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/datum.h"
+#include "utils/fmgroids.h"
#include "utils/expandeddatum.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
@@ -159,6 +162,33 @@ typedef struct WindowStatePerAggData
bool restart; /* need to restart this agg in this cycle? */
} WindowStatePerAggData;
+/*
+ * Set of StringInfo. Used in RPR.
+ */
+#define STRSET_FROZEN (1 << 0) /* string is frozen */
+#define STRSET_DISCARDED (1 << 1) /* string is scheduled to be discarded */
+#define STRSET_MATCHED (1 << 2) /* string is confirmed to be matched
+ * with pattern */
+
+typedef struct StringSet
+{
+ StringInfo *str_set;
+ Size set_size; /* current array allocation size in number of
+ * items */
+ int set_index; /* current used size */
+ int *info; /* an array of information bit per StringInfo.
+ * see above */
+} StringSet;
+
+/*
+ * Structure used by check_rpr_navigation() and rpr_navigation_walker().
+ */
+typedef struct NavigationInfo
+{
+ bool is_prev; /* true if PREV */
+ int num_vars; /* number of var nodes */
+} NavigationInfo;
+
static void initialize_windowaggregate(WindowAggState *winstate,
WindowStatePerFunc perfuncstate,
WindowStatePerAgg peraggstate);
@@ -184,6 +214,7 @@ static void release_partition(WindowAggState *winstate);
static int row_is_in_frame(WindowAggState *winstate, int64 pos,
TupleTableSlot *slot);
+
static void update_frameheadpos(WindowAggState *winstate);
static void update_frametailpos(WindowAggState *winstate);
static void update_grouptailpos(WindowAggState *winstate);
@@ -195,9 +226,60 @@ static Datum GetAggInitVal(Datum textInitVal, Oid transtype);
static bool are_peers(WindowAggState *winstate, TupleTableSlot *slot1,
TupleTableSlot *slot2);
+
+static int WinGetSlotInFrame(WindowObject winobj, TupleTableSlot *slot,
+ int relpos, int seektype, bool set_mark,
+ bool *isnull, bool *isout);
static bool window_gettupleslot(WindowObject winobj, int64 pos,
TupleTableSlot *slot);
+static void attno_map(Node *node);
+static bool attno_map_walker(Node *node, void *context);
+static int row_is_in_reduced_frame(WindowObject winobj, int64 pos);
+static bool rpr_is_defined(WindowAggState *winstate);
+
+static void create_reduced_frame_map(WindowAggState *winstate);
+static int get_reduced_frame_map(WindowAggState *winstate, int64 pos);
+static void register_reduced_frame_map(WindowAggState *winstate, int64 pos,
+ int val);
+static void clear_reduced_frame_map(WindowAggState *winstate);
+static void update_reduced_frame(WindowObject winobj, int64 pos);
+
+static int64 evaluate_pattern(WindowObject winobj, int64 current_pos,
+ char *vname, StringInfo encoded_str, bool *result);
+
+static bool get_slots(WindowObject winobj, int64 current_pos);
+
+static int search_str_set(WindowAggState *winstate,
+ StringSet *input_str_set);
+static StringSet *generate_patterns(StringSet *input_str_set,
+ char *pattern, VariablePos *variable_pos,
+ char tail_pattern_initial);
+static int add_pattern(StringInfo old, int old_info, StringSet *new_str_set,
+ char c, char *pattern, char tail_pattern_initial,
+ int resultlen);
+static int freeze_pattern(StringInfo old, int old_info,
+ StringSet *new_str_set,
+ char *pattern, int resultlen);
+static char pattern_initial(WindowAggState *winstate, char *vname);
+static int do_pattern_match(char *pattern, char *encoded_str, int len);
+
+static StringSet *string_set_init(void);
+static void string_set_add(StringSet *string_set, StringInfo str, int info);
+static StringInfo string_set_get(StringSet *string_set, int index, int *flag);
+static int string_set_get_size(StringSet *string_set);
+static void string_set_discard(StringSet *string_set);
+static VariablePos *variable_pos_init(void);
+static void variable_pos_register(VariablePos *variable_pos, char initial,
+ int pos);
+static bool variable_pos_compare(VariablePos *variable_pos,
+ char initial1, char initial2);
+static int variable_pos_fetch(VariablePos *variable_pos, char initial,
+ int index);
+static VariablePos *variable_pos_build(WindowAggState *winstate);
+
+static void check_rpr_navigation(Node *node, bool is_prev);
+static bool rpr_navigation_walker(Node *node, void *context);
/*
* initialize_windowaggregate
@@ -774,10 +856,12 @@ eval_windowaggregates(WindowAggState *winstate)
* transition function, or
* - we have an EXCLUSION clause, or
* - if the new frame doesn't overlap the old one
+ * - if RPR is enabled
*
* Note that we don't strictly need to restart in the last case, but if
* we're going to remove all rows from the aggregation anyway, a restart
* surely is faster.
+ * we restart aggregation too.
*----------
*/
numaggs_restart = 0;
@@ -788,7 +872,8 @@ eval_windowaggregates(WindowAggState *winstate)
(winstate->aggregatedbase != winstate->frameheadpos &&
!OidIsValid(peraggstate->invtransfn_oid)) ||
(winstate->frameOptions & FRAMEOPTION_EXCLUSION) ||
- winstate->aggregatedupto <= winstate->frameheadpos)
+ winstate->aggregatedupto <= winstate->frameheadpos ||
+ rpr_is_defined(winstate))
{
peraggstate->restart = true;
numaggs_restart++;
@@ -862,7 +947,22 @@ eval_windowaggregates(WindowAggState *winstate)
* head, so that tuplestore can discard unnecessary rows.
*/
if (agg_winobj->markptr >= 0)
- WinSetMarkPosition(agg_winobj, winstate->frameheadpos);
+ {
+ int64 markpos = winstate->frameheadpos;
+
+ if (rpr_is_defined(winstate))
+ {
+ /*
+ * If RPR is used, it is possible PREV wants to look at the
+ * previous row. So the mark pos should be frameheadpos - 1
+ * unless it is below 0.
+ */
+ markpos -= 1;
+ if (markpos < 0)
+ markpos = 0;
+ }
+ WinSetMarkPosition(agg_winobj, markpos);
+ }
/*
* Now restart the aggregates that require it.
@@ -917,6 +1017,14 @@ eval_windowaggregates(WindowAggState *winstate)
{
winstate->aggregatedupto = winstate->frameheadpos;
ExecClearTuple(agg_row_slot);
+
+ /*
+ * If RPR is defined, we do not use aggregatedupto_nonrestarted. To
+ * avoid assertion failure below, we reset aggregatedupto_nonrestarted
+ * to frameheadpos.
+ */
+ if (rpr_is_defined(winstate))
+ aggregatedupto_nonrestarted = winstate->frameheadpos;
}
/*
@@ -930,6 +1038,12 @@ eval_windowaggregates(WindowAggState *winstate)
{
int ret;
+#ifdef RPR_DEBUG
+ elog(DEBUG1, "===== loop in frame starts: aggregatedupto: " INT64_FORMAT " aggregatedbase: " INT64_FORMAT,
+ winstate->aggregatedupto,
+ winstate->aggregatedbase);
+#endif
+
/* Fetch next row if we didn't already */
if (TupIsNull(agg_row_slot))
{
@@ -945,9 +1059,53 @@ eval_windowaggregates(WindowAggState *winstate)
ret = row_is_in_frame(winstate, winstate->aggregatedupto, agg_row_slot);
if (ret < 0)
break;
+
if (ret == 0)
goto next_tuple;
+ if (rpr_is_defined(winstate))
+ {
+#ifdef RPR_DEBUG
+ elog(DEBUG1, "reduced_frame_map: %d aggregatedupto: " INT64_FORMAT " aggregatedbase: " INT64_FORMAT,
+ get_reduced_frame_map(winstate,
+ winstate->aggregatedupto),
+ winstate->aggregatedupto,
+ winstate->aggregatedbase);
+#endif
+
+ /*
+ * If the row status at currentpos is already decided and current
+ * row status is not decided yet, it means we passed the last
+ * reduced frame. Time to break the loop.
+ */
+ if (get_reduced_frame_map(winstate, winstate->currentpos)
+ != RF_NOT_DETERMINED &&
+ get_reduced_frame_map(winstate, winstate->aggregatedupto)
+ == RF_NOT_DETERMINED)
+ break;
+
+ /*
+ * Otherwise we need to calculate the reduced frame.
+ */
+ ret = row_is_in_reduced_frame(winstate->agg_winobj,
+ winstate->aggregatedupto);
+ if (ret == -1) /* unmatched row */
+ break;
+
+ /*
+ * Check if current row needs to be skipped due to no match.
+ */
+ if (get_reduced_frame_map(winstate,
+ winstate->aggregatedupto) == RF_SKIPPED &&
+ winstate->aggregatedupto == winstate->aggregatedbase)
+ {
+#ifdef RPR_DEBUG
+ elog(DEBUG1, "skip current row for aggregation");
+#endif
+ break;
+ }
+ }
+
/* Set tuple context for evaluation of aggregate arguments */
winstate->tmpcontext->ecxt_outertuple = agg_row_slot;
@@ -976,6 +1134,7 @@ next_tuple:
ExecClearTuple(agg_row_slot);
}
+
/* The frame's end is not supposed to move backwards, ever */
Assert(aggregatedupto_nonrestarted <= winstate->aggregatedupto);
@@ -1199,6 +1358,7 @@ begin_partition(WindowAggState *winstate)
winstate->framehead_valid = false;
winstate->frametail_valid = false;
winstate->grouptail_valid = false;
+ create_reduced_frame_map(winstate);
winstate->spooled_rows = 0;
winstate->currentpos = 0;
winstate->frameheadpos = 0;
@@ -2170,6 +2330,11 @@ ExecWindowAgg(PlanState *pstate)
CHECK_FOR_INTERRUPTS();
+#ifdef RPR_DEBUG
+ elog(DEBUG1, "ExecWindowAgg called. pos: " INT64_FORMAT,
+ winstate->currentpos);
+#endif
+
if (winstate->status == WINDOWAGG_DONE)
return NULL;
@@ -2278,6 +2443,17 @@ ExecWindowAgg(PlanState *pstate)
/* don't evaluate the window functions when we're in pass-through mode */
if (winstate->status == WINDOWAGG_RUN)
{
+ /*
+ * If RPR is defined and skip mode is next row, we need to clear
+ * existing reduced frame info so that we newly calculate the info
+ * starting from current row.
+ */
+ if (rpr_is_defined(winstate))
+ {
+ if (winstate->rpSkipTo == ST_NEXT_ROW)
+ clear_reduced_frame_map(winstate);
+ }
+
/*
* Evaluate true window functions
*/
@@ -2444,6 +2620,9 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
TupleDesc scanDesc;
ListCell *l;
+ TargetEntry *te;
+ Expr *expr;
+
/* check for unsupported flags */
Assert(!(eflags & (EXEC_FLAG_BACKWARD | EXEC_FLAG_MARK)));
@@ -2542,6 +2721,16 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
winstate->temp_slot_2 = ExecInitExtraTupleSlot(estate, scanDesc,
&TTSOpsMinimalTuple);
+ winstate->prev_slot = ExecInitExtraTupleSlot(estate, scanDesc,
+ &TTSOpsMinimalTuple);
+
+ winstate->next_slot = ExecInitExtraTupleSlot(estate, scanDesc,
+ &TTSOpsMinimalTuple);
+
+ winstate->null_slot = ExecInitExtraTupleSlot(estate, scanDesc,
+ &TTSOpsMinimalTuple);
+ winstate->null_slot = ExecStoreAllNullTuple(winstate->null_slot);
+
/*
* create frame head and tail slots only if needed (must create slots in
* exactly the same cases that update_frameheadpos and update_frametailpos
@@ -2723,6 +2912,49 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
winstate->inRangeAsc = node->inRangeAsc;
winstate->inRangeNullsFirst = node->inRangeNullsFirst;
+ /* Set up SKIP TO type */
+ winstate->rpSkipTo = node->rpSkipTo;
+ /* Set up row pattern recognition PATTERN clause */
+ winstate->patternVariableList = node->patternVariable;
+ winstate->patternRegexpList = node->patternRegexp;
+
+ /* Set up row pattern recognition DEFINE clause */
+ winstate->defineInitial = node->defineInitial;
+ winstate->defineVariableList = NIL;
+ winstate->defineClauseList = NIL;
+ if (node->defineClause != NIL)
+ {
+ /*
+ * Tweak arg var of PREV/NEXT so that it refers to scan/inner slot.
+ */
+ foreach(l, node->defineClause)
+ {
+ char *name;
+ ExprState *exps;
+
+ te = lfirst(l);
+ name = te->resname;
+ expr = te->expr;
+
+#ifdef RPR_DEBUG
+ elog(DEBUG1, "defineVariable name: %s", name);
+#endif
+ winstate->defineVariableList =
+ lappend(winstate->defineVariableList,
+ makeString(pstrdup(name)));
+ attno_map((Node *) expr);
+ exps = ExecInitExpr(expr, (PlanState *) winstate);
+ winstate->defineClauseList =
+ lappend(winstate->defineClauseList, exps);
+ }
+ }
+
+ /*
+ * Build variable_pos
+ */
+ if (winstate->defineInitial)
+ winstate->variable_pos = variable_pos_build(winstate);
+
winstate->all_first = true;
winstate->partition_spooled = false;
winstate->more_partitions = false;
@@ -2731,6 +2963,111 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
return winstate;
}
+/*
+ * Rewrite varno of Var nodes that are the argument of PREV/NET so that they
+ * see scan tuple (PREV) or inner tuple (NEXT). Also we check the arguments
+ * of PREV/NEXT include at least 1 column reference. This is required by the
+ * SQL standard.
+ */
+static void
+attno_map(Node *node)
+{
+ (void) expression_tree_walker(node, attno_map_walker, NULL);
+}
+
+static bool
+attno_map_walker(Node *node, void *context)
+{
+ FuncExpr *func;
+ int nargs;
+ bool is_prev;
+
+ if (node == NULL)
+ return false;
+
+ if (IsA(node, FuncExpr))
+ {
+ func = (FuncExpr *) node;
+
+ if (func->funcid == F_PREV || func->funcid == F_NEXT)
+ {
+ /*
+ * The SQL standard allows to have two more arguments form of
+ * PREV/NEXT. But currently we allow only 1 argument form.
+ */
+ nargs = list_length(func->args);
+ if (list_length(func->args) != 1)
+ elog(ERROR, "PREV/NEXT must have 1 argument but function %d has %d args",
+ func->funcid, nargs);
+
+ /*
+ * Check expr of PREV/NEXT aruguments and replace varno.
+ */
+ is_prev = (func->funcid == F_PREV) ? true : false;
+ check_rpr_navigation(node, is_prev);
+ }
+ }
+ return expression_tree_walker(node, attno_map_walker, NULL);
+}
+
+/*
+ * Rewrite varno of Var of RPR navigation operations (PREV/NEXT).
+ * If is_prev is true, we take care PREV, otherwise NEXT.
+ */
+static void
+check_rpr_navigation(Node *node, bool is_prev)
+{
+ NavigationInfo context;
+
+ context.is_prev = is_prev;
+ context.num_vars = 0;
+ (void) expression_tree_walker(node, rpr_navigation_walker, &context);
+ if (context.num_vars < 1)
+ ereport(ERROR,
+ errmsg("row pattern navigation operation's argument must include at least one column reference"));
+}
+
+static bool
+rpr_navigation_walker(Node *node, void *context)
+{
+ NavigationInfo *nav = (NavigationInfo *) context;
+
+ if (node == NULL)
+ return false;
+
+ switch (nodeTag(node))
+ {
+ case T_Var:
+ {
+ Var *var = (Var *) node;
+
+ nav->num_vars++;
+
+ if (nav->is_prev)
+ {
+ /*
+ * Rewrite varno from OUTER_VAR to regular var no so that
+ * the var references scan tuple.
+ */
+ var->varno = var->varnosyn;
+ }
+ else
+ var->varno = INNER_VAR;
+ }
+ break;
+ case T_Const:
+ case T_FuncExpr:
+ case T_OpExpr:
+ break;
+
+ default:
+ ereport(ERROR,
+ errmsg("row pattern navigation operation's argument includes unsupported expression"));
+ }
+ return expression_tree_walker(node, rpr_navigation_walker, context);
+}
+
+
/* -----------------
* ExecEndWindowAgg
* -----------------
@@ -2788,6 +3125,8 @@ ExecReScanWindowAgg(WindowAggState *node)
ExecClearTuple(node->agg_row_slot);
ExecClearTuple(node->temp_slot_1);
ExecClearTuple(node->temp_slot_2);
+ ExecClearTuple(node->prev_slot);
+ ExecClearTuple(node->next_slot);
if (node->framehead_slot)
ExecClearTuple(node->framehead_slot);
if (node->frametail_slot)
@@ -3148,7 +3487,8 @@ window_gettupleslot(WindowObject winobj, int64 pos, TupleTableSlot *slot)
return false;
if (pos < winobj->markpos)
- elog(ERROR, "cannot fetch row before WindowObject's mark position");
+ elog(ERROR, "cannot fetch row: " INT64_FORMAT " before WindowObject's mark position: " INT64_FORMAT,
+ pos, winobj->markpos);
oldcontext = MemoryContextSwitchTo(winstate->ss.ps.ps_ExprContext->ecxt_per_query_memory);
@@ -3468,14 +3808,54 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
WindowAggState *winstate;
ExprContext *econtext;
TupleTableSlot *slot;
- int64 abs_pos;
- int64 mark_pos;
Assert(WindowObjectIsValid(winobj));
winstate = winobj->winstate;
econtext = winstate->ss.ps.ps_ExprContext;
slot = winstate->temp_slot_1;
+ if (WinGetSlotInFrame(winobj, slot,
+ relpos, seektype, set_mark,
+ isnull, isout) == 0)
+ {
+ econtext->ecxt_outertuple = slot;
+ return ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno),
+ econtext, isnull);
+ }
+
+ if (isout)
+ *isout = true;
+ *isnull = true;
+ return (Datum) 0;
+}
+
+/*
+ * WinGetSlotInFrame
+ * slot: TupleTableSlot to store the result
+ * relpos: signed rowcount offset from the seek position
+ * seektype: WINDOW_SEEK_HEAD or WINDOW_SEEK_TAIL
+ * set_mark: If the row is found/in frame and set_mark is true, the mark is
+ * moved to the row as a side-effect.
+ * isnull: output argument, receives isnull status of result
+ * isout: output argument, set to indicate whether target row position
+ * is out of frame (can pass NULL if caller doesn't care about this)
+ *
+ * Returns 0 if we successfullt got the slot. false if out of frame.
+ * (also isout is set)
+ */
+static int
+WinGetSlotInFrame(WindowObject winobj, TupleTableSlot *slot,
+ int relpos, int seektype, bool set_mark,
+ bool *isnull, bool *isout)
+{
+ WindowAggState *winstate;
+ int64 abs_pos;
+ int64 mark_pos;
+ int num_reduced_frame;
+
+ Assert(WindowObjectIsValid(winobj));
+ winstate = winobj->winstate;
+
switch (seektype)
{
case WINDOW_SEEK_CURRENT:
@@ -3542,11 +3922,25 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
winstate->frameOptions);
break;
}
+ num_reduced_frame = row_is_in_reduced_frame(winobj,
+ winstate->frameheadpos);
+ if (num_reduced_frame < 0)
+ goto out_of_frame;
+ else if (num_reduced_frame > 0)
+ if (relpos >= num_reduced_frame)
+ goto out_of_frame;
break;
case WINDOW_SEEK_TAIL:
/* rejecting relpos > 0 is easy and simplifies code below */
if (relpos > 0)
goto out_of_frame;
+
+ /*
+ * RPR cares about frame head pos. Need to call
+ * update_frameheadpos
+ */
+ update_frameheadpos(winstate);
+
update_frametailpos(winstate);
abs_pos = winstate->frametailpos - 1 + relpos;
@@ -3613,6 +4007,14 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
mark_pos = 0; /* keep compiler quiet */
break;
}
+
+ num_reduced_frame = row_is_in_reduced_frame(winobj,
+ winstate->frameheadpos + relpos);
+ if (num_reduced_frame < 0)
+ goto out_of_frame;
+ else if (num_reduced_frame > 0)
+ abs_pos = winstate->frameheadpos + relpos +
+ num_reduced_frame - 1;
break;
default:
elog(ERROR, "unrecognized window seek type: %d", seektype);
@@ -3631,15 +4033,13 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
*isout = false;
if (set_mark)
WinSetMarkPosition(winobj, mark_pos);
- econtext->ecxt_outertuple = slot;
- return ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno),
- econtext, isnull);
+ return 0;
out_of_frame:
if (isout)
*isout = true;
*isnull = true;
- return (Datum) 0;
+ return -1;
}
/*
@@ -3670,3 +4070,1420 @@ WinGetFuncArgCurrent(WindowObject winobj, int argno, bool *isnull)
return ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno),
econtext, isnull);
}
+
+/*
+ * rpr_is_defined
+ * return true if Row pattern recognition is defined.
+ */
+static
+bool
+rpr_is_defined(WindowAggState *winstate)
+{
+ return winstate->patternVariableList != NIL;
+}
+
+/*
+ * -----------------
+ * row_is_in_reduced_frame
+ * Determine whether a row is in the current row's reduced window frame
+ * according to row pattern matching
+ *
+ * The row must has been already determined that it is in a full window frame
+ * and fetched it into slot.
+ *
+ * Returns:
+ * = 0, RPR is not defined.
+ * >0, if the row is the first in the reduced frame. Return the number of rows
+ * in the reduced frame.
+ * -1, if the row is unmatched row
+ * -2, if the row is in the reduced frame but needed to be skipped because of
+ * AFTER MATCH SKIP PAST LAST ROW
+ * -----------------
+ */
+static
+int
+row_is_in_reduced_frame(WindowObject winobj, int64 pos)
+{
+ WindowAggState *winstate = winobj->winstate;
+ int state;
+ int rtn;
+
+ if (!rpr_is_defined(winstate))
+ {
+ /*
+ * RPR is not defined. Assume that we are always in the the reduced
+ * window frame.
+ */
+ rtn = 0;
+#ifdef RPR_DEBUG
+ elog(DEBUG1, "row_is_in_reduced_frame returns %d: pos: " INT64_FORMAT,
+ rtn, pos);
+#endif
+ return rtn;
+ }
+
+ state = get_reduced_frame_map(winstate, pos);
+
+ if (state == RF_NOT_DETERMINED)
+ {
+ update_frameheadpos(winstate);
+ update_reduced_frame(winobj, pos);
+ }
+
+ state = get_reduced_frame_map(winstate, pos);
+
+ switch (state)
+ {
+ int64 i;
+ int num_reduced_rows;
+
+ case RF_FRAME_HEAD:
+ num_reduced_rows = 1;
+ for (i = pos + 1;
+ get_reduced_frame_map(winstate, i) == RF_SKIPPED; i++)
+ num_reduced_rows++;
+ rtn = num_reduced_rows;
+ break;
+
+ case RF_SKIPPED:
+ rtn = -2;
+ break;
+
+ case RF_UNMATCHED:
+ rtn = -1;
+ break;
+
+ default:
+ elog(ERROR, "Unrecognized state: %d at: " INT64_FORMAT,
+ state, pos);
+ break;
+ }
+
+#ifdef RPR_DEBUG
+ elog(DEBUG1, "row_is_in_reduced_frame returns %d: pos: " INT64_FORMAT,
+ rtn, pos);
+#endif
+ return rtn;
+}
+
+#define REDUCED_FRAME_MAP_INIT_SIZE 1024L
+
+/*
+ * create_reduced_frame_map
+ * Create reduced frame map
+ */
+static
+void
+create_reduced_frame_map(WindowAggState *winstate)
+{
+ winstate->reduced_frame_map =
+ MemoryContextAlloc(winstate->partcontext,
+ REDUCED_FRAME_MAP_INIT_SIZE);
+ winstate->alloc_sz = REDUCED_FRAME_MAP_INIT_SIZE;
+ clear_reduced_frame_map(winstate);
+}
+
+/*
+ * clear_reduced_frame_map
+ * Clear reduced frame map
+ */
+static
+void
+clear_reduced_frame_map(WindowAggState *winstate)
+{
+ Assert(winstate->reduced_frame_map != NULL);
+ MemSet(winstate->reduced_frame_map, RF_NOT_DETERMINED,
+ winstate->alloc_sz);
+}
+
+/*
+ * get_reduced_frame_map
+ * Get reduced frame map specified by pos
+ */
+static
+int
+get_reduced_frame_map(WindowAggState *winstate, int64 pos)
+{
+ Assert(winstate->reduced_frame_map != NULL);
+ Assert(pos >= 0);
+
+ /*
+ * If pos is not in the reduced frame map, it means that any info
+ * regarding the pos has not been registered yet. So we return
+ * RF_NOT_DETERMINED.
+ */
+ if (pos >= winstate->alloc_sz)
+ return RF_NOT_DETERMINED;
+
+ return winstate->reduced_frame_map[pos];
+}
+
+/*
+ * register_reduced_frame_map
+ * Add/replace reduced frame map member at pos.
+ * If there's no enough space, expand the map.
+ */
+static
+void
+register_reduced_frame_map(WindowAggState *winstate, int64 pos, int val)
+{
+ int64 realloc_sz;
+
+ Assert(winstate->reduced_frame_map != NULL);
+
+ if (pos < 0)
+ elog(ERROR, "wrong pos: " INT64_FORMAT, pos);
+
+ if (pos > winstate->alloc_sz - 1)
+ {
+ realloc_sz = winstate->alloc_sz * 2;
+
+ winstate->reduced_frame_map =
+ repalloc(winstate->reduced_frame_map, realloc_sz);
+
+ MemSet(winstate->reduced_frame_map + winstate->alloc_sz,
+ RF_NOT_DETERMINED, realloc_sz - winstate->alloc_sz);
+
+ winstate->alloc_sz = realloc_sz;
+ }
+
+ winstate->reduced_frame_map[pos] = val;
+}
+
+/*
+ * update_reduced_frame
+ * Update reduced frame info.
+ */
+static
+void
+update_reduced_frame(WindowObject winobj, int64 pos)
+{
+ WindowAggState *winstate = winobj->winstate;
+ ListCell *lc1,
+ *lc2;
+ bool expression_result;
+ int num_matched_rows;
+ int64 original_pos;
+ bool anymatch;
+ StringInfo encoded_str;
+ StringSet *str_set;
+ bool greedy = false;
+ int64 result_pos, i;
+ int init_size;
+
+ /*
+ * Set of pattern variables evaluated to true. Each character corresponds
+ * to pattern variable. Example: str_set[0] = "AB"; str_set[1] = "AC"; In
+ * this case at row 0 A and B are true, and A and C are true in row 1.
+ */
+
+ /* initialize pattern variables set */
+ str_set = string_set_init();
+
+ /* save original pos */
+ original_pos = pos;
+
+ /*
+ * Calculate initial memory allocation size from the number of pattern
+ * variables,
+ */
+ init_size = sizeof(char) * list_length(winstate->patternVariableList) + 1;
+
+ /*
+ * Check if the pattern does not include any greedy quantifier. If it does
+ * not, we can just apply the pattern to each row. If it succeeds, we are
+ * done.
+ */
+ foreach(lc1, winstate->patternRegexpList)
+ {
+ char *quantifier = strVal(lfirst(lc1));
+
+ if (*quantifier == '+' || *quantifier == '*')
+ {
+ greedy = true;
+ break;
+ }
+ }
+
+ /*
+ * Non greedy case
+ */
+ if (!greedy)
+ {
+ num_matched_rows = 0;
+ encoded_str = makeStringInfoExt(init_size);
+
+ foreach(lc1, winstate->patternVariableList)
+ {
+ char *vname = strVal(lfirst(lc1));
+#ifdef RPR_DEBUG
+ elog(DEBUG1, "pos: " INT64_FORMAT " pattern vname: %s",
+ pos, vname);
+#endif
+ expression_result = false;
+
+ /* evaluate row pattern against current row */
+ result_pos = evaluate_pattern(winobj, pos, vname,
+ encoded_str, &expression_result);
+ if (!expression_result || result_pos < 0)
+ {
+#ifdef RPR_DEBUG
+ elog(DEBUG1, "expression result is false or out of frame");
+#endif
+ register_reduced_frame_map(winstate, original_pos,
+ RF_UNMATCHED);
+ destroyStringInfo(encoded_str);
+ return;
+ }
+ /* move to next row */
+ pos++;
+ num_matched_rows++;
+ }
+ destroyStringInfo(encoded_str);
+#ifdef RPR_DEBUG
+ elog(DEBUG1, "pattern matched");
+#endif
+ register_reduced_frame_map(winstate, original_pos, RF_FRAME_HEAD);
+
+ for (i = original_pos + 1; i < original_pos + num_matched_rows; i++)
+ {
+ register_reduced_frame_map(winstate, i, RF_SKIPPED);
+ }
+ return;
+ }
+
+ /*
+ * Greedy quantifiers included. Loop over until none of pattern matches or
+ * encounters end of frame.
+ */
+ for (;;)
+ {
+ result_pos = -1;
+
+ /*
+ * Loop over each PATTERN variable.
+ */
+ anymatch = false;
+
+ encoded_str = makeStringInfoExt(init_size);
+
+ forboth(lc1, winstate->patternVariableList, lc2,
+ winstate->patternRegexpList)
+ {
+ char *vname = strVal(lfirst(lc1));
+#ifdef RPR_DEBUG
+ char *quantifier = strVal(lfirst(lc2));
+
+ elog(DEBUG1, "pos: " INT64_FORMAT " pattern vname: %s quantifier: %s",
+ pos, vname, quantifier);
+#endif
+ expression_result = false;
+
+ /* evaluate row pattern against current row */
+ result_pos = evaluate_pattern(winobj, pos, vname,
+ encoded_str, &expression_result);
+ if (expression_result)
+ {
+#ifdef RPR_DEBUG
+ elog(DEBUG1, "expression result is true");
+#endif
+ anymatch = true;
+ }
+
+ /*
+ * If out of frame, we are done.
+ */
+ if (result_pos < 0)
+ break;
+ }
+
+ if (!anymatch)
+ {
+ /* none of patterns matched. */
+ break;
+ }
+
+ string_set_add(str_set, encoded_str, 0);
+
+#ifdef RPR_DEBUG
+ elog(DEBUG1, "pos: " INT64_FORMAT " encoded_str: %s",
+ encoded_str->data);
+#endif
+
+ /* move to next row */
+ pos++;
+
+ if (result_pos < 0)
+ {
+ /* out of frame */
+ break;
+ }
+ }
+
+ if (string_set_get_size(str_set) == 0)
+ {
+ /* no match found in the first row */
+ register_reduced_frame_map(winstate, original_pos, RF_UNMATCHED);
+ destroyStringInfo(encoded_str);
+ return;
+ }
+
+#ifdef RPR_DEBUG
+ elog(DEBUG2, "pos: " INT64_FORMAT " encoded_str: %s",
+ pos, encoded_str->data);
+#endif
+
+ /* look for matching pattern variable sequence */
+#ifdef RPR_DEBUG
+ elog(DEBUG1, "search_str_set started");
+#endif
+ num_matched_rows = search_str_set(winstate, str_set);
+
+#ifdef RPR_DEBUG
+ elog(DEBUG1, "search_str_set returns: %d", num_matched_rows);
+#endif
+ string_set_discard(str_set);
+
+ /*
+ * We are at the first row in the reduced frame. Save the number of
+ * matched rows as the number of rows in the reduced frame.
+ */
+ if (num_matched_rows <= 0)
+ {
+ /* no match */
+ register_reduced_frame_map(winstate, original_pos, RF_UNMATCHED);
+ }
+ else
+ {
+ register_reduced_frame_map(winstate, original_pos, RF_FRAME_HEAD);
+
+ for (i = original_pos + 1; i < original_pos + num_matched_rows; i++)
+ {
+ register_reduced_frame_map(winstate, i, RF_SKIPPED);
+ }
+ }
+
+ return;
+}
+
+/*
+ * search_str_set
+ *
+ * Perform pattern matching using "pattern" against input_str_set. pattern is
+ * a regular expression string derived from PATTERN clause. Note that the
+ * regular expression string is prefixed by '^' and followed by initials
+ * represented in a same way as str_set. str_set is a set of StringInfo. Each
+ * StringInfo has a string comprising initials of pattern variable strings
+ * being true in a row. The initials are one of [a-z], parallel to the order
+ * of variable names in DEFINE clause. Suppose DEFINE has variables START, UP
+ * and DOWN. If PATTERN has START, UP+ and DOWN, then the initials in PATTERN
+ * will be 'a', 'b' and 'c'. The "pattern" will be "^ab+c".
+ *
+ * variable_pos is an array representing the order of pattern variable string
+ * initials in PATTERN clause. For example initial 'a' potion is in
+ * variable_pos[0].pos[0] = 0. Note that if the pattern is "START UP DOWN UP"
+ * (UP appears twice), then "UP" (initial is 'b') has two position 1 and
+ * 3. Thus variable_pos for b is variable_pos[1].pos[0] = 1 and
+ * variable_pos[1].pos[1] = 3.
+ *
+ * Returns the longest number of the matching rows (greedy matching) if
+ * quatifier '+' or '*' is included in "pattern".
+ */
+static
+int
+search_str_set(WindowAggState *winstate, StringSet *input_str_set)
+{
+ char *pattern; /* search regexp pattern */
+ VariablePos *variable_pos;
+ int set_size; /* number of rows in the set */
+ int resultlen;
+ int index;
+ StringSet *new_str_set;
+ int new_str_size;
+ int len;
+ int info;
+ char tail_pattern_initial;
+
+ /*
+ * Set last initial char to tail_pattern_initial if we can apply "tail
+ * pattern initial optimization". If the last regexp component in pattern
+ * is with '+' quatifier, set the initial to tail_pattern_initial. For
+ * example if pattern = "ab+", tail_pattern_initial will be 'b'.
+ * Otherwise, tail_pattern_initial is '\0'.
+ */
+ pattern = winstate->pattern_str->data;
+ if (pattern[strlen(pattern) - 1] == '+')
+ tail_pattern_initial = pattern[strlen(pattern) - 2];
+ else
+ tail_pattern_initial = '\0';
+
+ /*
+ * Generate all possible pattern variable name initials as a set of
+ * StringInfo named "new_str_set". For example, if we have two rows
+ * having "ab" (row 0) and "ac" (row 1) in the input str_set, new_str_set
+ * will have set of StringInfo "aa", "ac", "ba" and "bc" in the end.
+ */
+ variable_pos = winstate->variable_pos;
+ new_str_set = generate_patterns(input_str_set, pattern, variable_pos,
+ tail_pattern_initial);
+
+ /*
+ * Perform pattern matching to find out the longest match.
+ */
+ new_str_size = string_set_get_size(new_str_set);
+ len = 0;
+ resultlen = 0;
+ set_size = string_set_get_size(input_str_set);
+
+ for (index = 0; index < new_str_size; index++)
+ {
+ StringInfo s;
+
+ s = string_set_get(new_str_set, index, &info);
+ if (s == NULL)
+ continue; /* no data */
+
+ /*
+ * If the string is scheduled to be discarded, we just disregard it.
+ */
+ if (info & STRSET_DISCARDED)
+ continue;
+
+ len = do_pattern_match(pattern, s->data, s->len);
+ if (len > resultlen)
+ {
+ /* remember the longest match */
+ resultlen = len;
+
+ /*
+ * If the size of result set is equal to the number of rows in the
+ * set, we are done because it's not possible that the number of
+ * matching rows exceeds the number of rows in the set.
+ */
+ if (resultlen >= set_size)
+ break;
+ }
+ }
+
+ /* we no longer need new string set */
+ string_set_discard(new_str_set);
+
+ return resultlen;
+}
+
+/*
+ * generate_patterns
+ *
+ * Generate all possible pattern variable name initials in 'input_str_set' as
+ * a set of StringInfo and return it. For example, if we have two rows having
+ * "ab" (row 0) and "ac" (row 1) in 'input str_set', returned StringSet will
+ * have set of StringInfo "aa", "ac", "ba" and "bc" in the end.
+ * 'variable_pos' and 'tail_pattern_initial' are used for pruning
+ * optimization.
+ */
+static
+StringSet *
+generate_patterns(StringSet *input_str_set, char *pattern,
+ VariablePos *variable_pos, char tail_pattern_initial)
+{
+ StringSet *old_str_set,
+ *new_str_set;
+ int index;
+ int set_size;
+ int old_set_size;
+ int info;
+ int resultlen;
+ StringInfo str;
+ int i;
+ char *p;
+
+ new_str_set = string_set_init();
+ set_size = string_set_get_size(input_str_set);
+ if (set_size == 0) /* if there's no row in input, return empty
+ * set */
+ return new_str_set;
+
+ resultlen = 0;
+
+ /*
+ * Generate initial new_string_set for input row 0.
+ */
+ str = string_set_get(input_str_set, 0, &info);
+ p = str->data;
+
+ /*
+ * Loop over each new pattern variable char.
+ */
+ while (*p)
+ {
+ StringInfo new = makeStringInfo();
+
+ /* add pattern variable char */
+ appendStringInfoChar(new, *p);
+ /* add new one to string set */
+ string_set_add(new_str_set, new, 0);
+ p++; /* next pattern variable */
+ }
+
+ /*
+ * Generate new_string_set for each input row.
+ */
+ for (index = 1; index < set_size; index++)
+ {
+ /* previous new str set now becomes old str set */
+ old_str_set = new_str_set;
+ new_str_set = string_set_init(); /* create new string set */
+ /* pick up input string */
+ str = string_set_get(input_str_set, index, &info);
+ old_set_size = string_set_get_size(old_str_set);
+
+ /*
+ * Loop over each row in the previous result set.
+ */
+ for (i = 0; i < old_set_size; i++)
+ {
+ char last_old_char;
+ int old_str_len;
+ int old_info;
+ StringInfo old;
+
+ old = string_set_get(old_str_set, i, &old_info);
+ p = old->data;
+ old_str_len = old->len;
+ if (old_str_len > 0)
+ last_old_char = p[old_str_len - 1];
+ else
+ last_old_char = '\0';
+
+ /* Can this old set be discarded? */
+ if (old_info & STRSET_DISCARDED)
+ continue; /* discard the old string */
+
+ /* Is this old set frozen? */
+ else if (old_info & STRSET_FROZEN)
+ {
+ /* if shorter match. we can discard it */
+ if (old_str_len < resultlen)
+ continue; /* discard the shorter string */
+
+ /* move the old set to new_str_set */
+ string_set_add(new_str_set, old, old_info);
+ old_str_set->str_set[i] = NULL;
+ continue;
+ }
+
+ /*
+ * loop over each pattern variable initial char in the input set.
+ */
+ for (p = str->data; *p; p++)
+ {
+ /*
+ * Optimization. Check if the row's pattern variable initial
+ * character position is greater than or equal to the old
+ * set's last pattern variable initial character position. For
+ * example, if the old set's last pattern variable initials
+ * are "ab", then the new pattern variable initial can be "b"
+ * or "c" but can not be "a", if the initials in PATTERN is
+ * something like "a b c" or "a b+ c+" etc. This optimization
+ * is possible when we only allow "+" quantifier.
+ */
+ if (variable_pos_compare(variable_pos, last_old_char, *p))
+
+ /*
+ * Satisfied the condition. Add new pattern char to
+ * new_str_set if it looks good.
+ */
+ resultlen = add_pattern(old, old_info, new_str_set, *p,
+ pattern, tail_pattern_initial, resultlen);
+ else
+
+ /*
+ * The old_str did not satisfy the condition and it cannot
+ * be extended further. "Freeze" it.
+ */
+ resultlen = freeze_pattern(old, old_info,
+ new_str_set, pattern, resultlen);
+ }
+ }
+ /* we no longer need old string set */
+ string_set_discard(old_str_set);
+ }
+ return new_str_set;
+}
+
+/*
+ * add_pattern
+ *
+ * Make a copy of 'old' (along with 'old_info' flag) and add new pattern char
+ * 'c' to it. Then add it to 'new_str_set'. 'pattern' and
+ * 'tail_pattern_initial' is checked to determine whether the copy is worth to
+ * add to new_str_set or not. The match length (possibly longer than
+ * 'resultlen') is returned.
+ */
+static
+int
+add_pattern(StringInfo old, int old_info, StringSet *new_str_set, char c,
+ char *pattern, char tail_pattern_initial, int resultlen)
+{
+ StringInfo new;
+ int info;
+ int len;
+
+ /*
+ * New char in the input row satisfies the condition above.
+ */
+ new = makeStringInfoExt(old->len + 1); /* copy source string */
+ appendStringInfoString(new, old->data);
+
+ /* add pattern variable char */
+ appendStringInfoChar(new, c);
+
+ /*
+ * Adhoc optimization. If the first letter in the input string is in the
+ * head and second position and there's no associated quatifier '+', then
+ * we can dicard the input because there's no chance to expand the string
+ * further.
+ *
+ * For example, pattern "abc" cannot match "aa".
+ */
+ if (pattern[1] == new->data[0] &&
+ pattern[1] == new->data[1] &&
+ pattern[2] != '+' &&
+ pattern[1] != pattern[2])
+ {
+ destroyStringInfo(new);
+ return resultlen;
+ }
+
+ info = old_info;
+
+ /*
+ * Check if we can apply "tail pattern initial optimization". If the last
+ * regexp component in pattern has '+' quantifier, the component is set to
+ * the last pattern initial. For example if pattern is "ab+",
+ * tail_pattern_initial will become 'b'. Otherwise, tail_pattern_initial
+ * is '\0'. If the tail pattern initial optimization is possible, we do
+ * not need to apply regular expression match again. Suppose we have the
+ * previous string ended with "b" and the it was confirmed the regular
+ * expression match, then char 'b' can be added to the string without
+ * applying the regular expression match again.
+ */
+ if (c == tail_pattern_initial) /* tail pattern initial optimization
+ * possible? */
+ {
+ /*
+ * Is already confirmed to be matched with pattern?
+ */
+ if ((info & STRSET_MATCHED) == 0)
+ {
+ /* not confirmed yet */
+ len = do_pattern_match(pattern, new->data, new->len);
+ if (len > 0)
+ info = STRSET_MATCHED; /* set already confirmed flag */
+ }
+ else
+
+ /*
+ * already confirmed. Use the string length as the matching length
+ */
+ len = new->len;
+
+ /* update the longest match length if needed */
+ if (len > resultlen)
+ resultlen = len;
+ }
+
+ /* add new StringInfo to the string set */
+ string_set_add(new_str_set, new, info);
+
+ return resultlen;
+}
+
+/*
+ * freeze_pattern
+ *
+ * "Freeze" 'old' (along with 'old_info' flag) and add it to
+ * 'new_str_set'. Frozen string is known to not be expanded further. The frozen
+ * string is check if it satisfies 'pattern'. If it does not, "discarded"
+ * mark is added. The discarded mark is also added if the match length is
+ * shorter than the current longest match length. The match length (possibly
+ * longer than 'resultlen') is returned.
+ */
+static
+int
+freeze_pattern(StringInfo old, int old_info, StringSet *new_str_set,
+ char *pattern, int resultlen)
+{
+ int len;
+ StringInfo new;
+ int new_str_size;
+ int new_index;
+
+ /*
+ * We are freezing this pattern string. If the pattern string length is
+ * shorter than the current longest string length, we don't need to keep
+ * it.
+ */
+ if (old->len < resultlen)
+ return resultlen;
+
+ if (old_info & STRSET_MATCHED)
+ /* we don't need to apply pattern match again */
+ len = old->len;
+ else
+ {
+ /* apply pattern match */
+ len = do_pattern_match(pattern, old->data, old->len);
+ if (len <= 0)
+ {
+ /* no match. we can discard it */
+ return resultlen;
+ }
+ }
+ if (len < resultlen)
+ {
+ /* shorter match. we can discard it */
+ return resultlen;
+ }
+
+ /*
+ * Match length is the longest so far
+ */
+ resultlen = len; /* remember the longest match */
+
+ /* freeze the pattern string */
+ new = makeStringInfo();
+ enlargeStringInfo(new, old->len + 1);
+ appendStringInfoString(new, old->data);
+ /* set frozen mark */
+ string_set_add(new_str_set, new, STRSET_FROZEN);
+
+ /*
+ * Search new_str_set to find out frozen entries that have shorter match
+ * length. Mark them as "discard" so that they are discarded in the next
+ * round.
+ */
+ new_str_size =
+ string_set_get_size(new_str_set) - 1;
+
+ /* loop over new_str_set */
+ for (new_index = 0; new_index < new_str_size; new_index++)
+ {
+ int info;
+
+ new = string_set_get(new_str_set, new_index, &info);
+
+ /*
+ * If this is frozen and is not longer than the current longest match
+ * length, we don't need to keep this.
+ */
+ if (info & STRSET_FROZEN && new->len < resultlen)
+ {
+ /*
+ * mark this set to discard in the next round
+ */
+ info |= STRSET_DISCARDED;
+ new_str_set->info[new_index] = info;
+ }
+ }
+ return resultlen;
+}
+
+/*
+ * do_pattern_match
+ *
+ * Perform pattern match using 'pattern' against 'encoded_str' whose length is
+ * 'len' bytes (without null terminate). Returns matching number of rows if
+ * matching is succeeded. Otherwise returns 0.
+ */
+static
+int
+do_pattern_match(char *pattern, char *encoded_str, int len)
+{
+ static regex_t *regcache = NULL;
+ static regex_t preg;
+ static char patbuf[1024]; /* most recent 'pattern' is cached here */
+ int plen;
+ int cflags = REG_EXTENDED;
+ size_t nmatch = 1;
+ int eflags = 0;
+ regmatch_t pmatch[1];
+ int sts;
+ pg_wchar *data;
+ int data_len;
+
+ /*
+ * Compile regexp if cache does not exist or existing cache is not same as
+ * "pattern".
+ */
+ if (strcmp(patbuf, pattern))
+ {
+ /*
+ * The compiled re must live in top memory context because patbuf is
+ * static data.
+ */
+ MemoryContext oldContext = MemoryContextSwitchTo(TopMemoryContext);
+
+ if (regcache != NULL)
+ pg_regfree(regcache); /* free previous re */
+
+ /* we need to convert to char to pg_wchar */
+ plen = strlen(pattern);
+ data = (pg_wchar *) palloc((plen + 1) * sizeof(pg_wchar));
+ data_len = pg_mb2wchar_with_len(pattern, data, plen);
+ /* compile re */
+ sts = pg_regcomp(&preg, /* compiled re */
+ data, /* target pattern */
+ data_len, /* length of pattern */
+ cflags, /* compile option */
+ C_COLLATION_OID /* collation */
+ );
+ pfree(data);
+
+ MemoryContextSwitchTo(oldContext);
+
+ if (sts != REG_OKAY)
+ {
+ /* re didn't compile (no need for pg_regfree, if so) */
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_REGULAR_EXPRESSION),
+ errmsg("invalid regular expression: %s", pattern)));
+ }
+
+ /* save cache */
+ regcache = &preg;
+ strncpy(patbuf, pattern, sizeof(patbuf));
+ }
+
+ data = (pg_wchar *) palloc((len + 1) * sizeof(pg_wchar));
+ data_len = pg_mb2wchar_with_len(encoded_str, data, len);
+
+ /* execute the regular expression match */
+ sts = pg_regexec(
+ &preg, /* compiled re */
+ data, /* target string */
+ data_len, /* length of encoded_str */
+ 0, /* search start */
+ NULL, /* rm details */
+ nmatch, /* number of match sub re */
+ pmatch, /* match result details */
+ eflags);
+
+ pfree(data);
+
+ if (sts != REG_OKAY)
+ {
+ if (sts != REG_NOMATCH)
+ {
+ char errMsg[100];
+
+ pg_regerror(sts, &preg, errMsg, sizeof(errMsg));
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_REGULAR_EXPRESSION),
+ errmsg("regular expression failed: %s", errMsg)));
+ }
+ return 0; /* does not match */
+ }
+
+ len = pmatch[0].rm_eo; /* return match length */
+ return len;
+
+}
+
+/*
+ * evaluate_pattern
+ *
+ * Evaluate expression associated with PATTERN variable vname. current_pos is
+ * relative row position in a frame (starting from 0). If vname is evaluated
+ * to true, initial letters associated with vname is appended to
+ * encode_str. result is out paramater representing the expression evaluation
+ * result is true of false.
+ *---------
+ * Return values are:
+ * >=0: the last match absolute row position
+ * otherwise out of frame.
+ *---------
+ */
+static
+int64
+evaluate_pattern(WindowObject winobj, int64 current_pos,
+ char *vname, StringInfo encoded_str, bool *result)
+{
+ WindowAggState *winstate = winobj->winstate;
+ ExprContext *econtext = winstate->ss.ps.ps_ExprContext;
+ ListCell *lc1,
+ *lc2,
+ *lc3;
+ ExprState *pat;
+ Datum eval_result;
+ bool out_of_frame = false;
+ bool isnull;
+ TupleTableSlot *slot;
+
+ forthree(lc1, winstate->defineVariableList,
+ lc2, winstate->defineClauseList,
+ lc3, winstate->defineInitial)
+ {
+ char initial; /* initial letter associated with vname */
+ char *name = strVal(lfirst(lc1));
+
+ if (strcmp(vname, name))
+ continue;
+
+ initial = *(strVal(lfirst(lc3)));
+
+ /* set expression to evaluate */
+ pat = lfirst(lc2);
+
+ /* get current, previous and next tuples */
+ if (!get_slots(winobj, current_pos))
+ {
+ out_of_frame = true;
+ }
+ else
+ {
+ /* evaluate the expression */
+ eval_result = ExecEvalExpr(pat, econtext, &isnull);
+ if (isnull)
+ {
+ /* expression is NULL */
+#ifdef RPR_DEBUG
+ elog(DEBUG1, "expression for %s is NULL at row: " INT64_FORMAT,
+ vname, current_pos);
+#endif
+ *result = false;
+ }
+ else
+ {
+ if (!DatumGetBool(eval_result))
+ {
+ /* expression is false */
+#ifdef RPR_DEBUG
+ elog(DEBUG1, "expression for %s is false at row: " INT64_FORMAT,
+ vname, current_pos);
+#endif
+ *result = false;
+ }
+ else
+ {
+ /* expression is true */
+#ifdef RPR_DEBUG
+ elog(DEBUG1, "expression for %s is true at row: " INT64_FORMAT,
+ vname, current_pos);
+#endif
+ appendStringInfoChar(encoded_str, initial);
+ *result = true;
+ }
+ }
+
+ slot = winstate->temp_slot_1;
+ if (slot != winstate->null_slot)
+ ExecClearTuple(slot);
+ slot = winstate->prev_slot;
+ if (slot != winstate->null_slot)
+ ExecClearTuple(slot);
+ slot = winstate->next_slot;
+ if (slot != winstate->null_slot)
+ ExecClearTuple(slot);
+
+ break;
+ }
+
+ if (out_of_frame)
+ {
+ *result = false;
+ return -1;
+ }
+ }
+ return current_pos;
+}
+
+/*
+ * get_slots
+ *
+ * Get current, previous and next tuples.
+ * Returns false if current row is out of partition/full frame.
+ */
+static
+bool
+get_slots(WindowObject winobj, int64 current_pos)
+{
+ WindowAggState *winstate = winobj->winstate;
+ TupleTableSlot *slot;
+ int ret;
+ ExprContext *econtext;
+
+ econtext = winstate->ss.ps.ps_ExprContext;
+
+ /* set up current row tuple slot */
+ slot = winstate->temp_slot_1;
+ if (!window_gettupleslot(winobj, current_pos, slot))
+ {
+#ifdef RPR_DEBUG
+ elog(DEBUG1, "current row is out of partition at:" INT64_FORMAT,
+ current_pos);
+#endif
+ return false;
+ }
+ ret = row_is_in_frame(winstate, current_pos, slot);
+ if (ret <= 0)
+ {
+#ifdef RPR_DEBUG
+ elog(DEBUG1, "current row is out of frame at: " INT64_FORMAT,
+ current_pos);
+#endif
+ ExecClearTuple(slot);
+ return false;
+ }
+ econtext->ecxt_outertuple = slot;
+
+ /* for PREV */
+ if (current_pos > 0)
+ {
+ slot = winstate->prev_slot;
+ if (!window_gettupleslot(winobj, current_pos - 1, slot))
+ {
+#ifdef RPR_DEBUG
+ elog(DEBUG1, "previous row is out of partition at: " INT64_FORMAT,
+ current_pos - 1);
+#endif
+ econtext->ecxt_scantuple = winstate->null_slot;
+ }
+ else
+ {
+ ret = row_is_in_frame(winstate, current_pos - 1, slot);
+ if (ret <= 0)
+ {
+#ifdef RPR_DEBUG
+ elog(DEBUG1, "previous row is out of frame at: " INT64_FORMAT,
+ current_pos - 1);
+#endif
+ ExecClearTuple(slot);
+ econtext->ecxt_scantuple = winstate->null_slot;
+ }
+ else
+ {
+ econtext->ecxt_scantuple = slot;
+ }
+ }
+ }
+ else
+ econtext->ecxt_scantuple = winstate->null_slot;
+
+ /* for NEXT */
+ slot = winstate->next_slot;
+ if (!window_gettupleslot(winobj, current_pos + 1, slot))
+ {
+#ifdef RPR_DEBUG
+ elog(DEBUG1, "next row is out of partiton at: " INT64_FORMAT,
+ current_pos + 1);
+#endif
+ econtext->ecxt_innertuple = winstate->null_slot;
+ }
+ else
+ {
+ ret = row_is_in_frame(winstate, current_pos + 1, slot);
+ if (ret <= 0)
+ {
+#ifdef RPR_DEBUG
+ elog(DEBUG1, "next row is out of frame at: " INT64_FORMAT,
+ current_pos + 1);
+#endif
+ ExecClearTuple(slot);
+ econtext->ecxt_innertuple = winstate->null_slot;
+ }
+ else
+ econtext->ecxt_innertuple = slot;
+ }
+ return true;
+}
+
+/*
+ * pattern_initial
+ *
+ * Return pattern variable initial character
+ * matching with pattern variable name vname.
+ * If not found, return 0.
+ */
+static
+char
+pattern_initial(WindowAggState *winstate, char *vname)
+{
+ char initial;
+ char *name;
+ ListCell *lc1,
+ *lc2;
+
+ forboth(lc1, winstate->defineVariableList,
+ lc2, winstate->defineInitial)
+ {
+ name = strVal(lfirst(lc1)); /* DEFINE variable name */
+ initial = *(strVal(lfirst(lc2))); /* DEFINE variable initial */
+
+
+ if (!strcmp(name, vname))
+ return initial; /* found */
+ }
+ return 0;
+}
+
+/*
+ * string_set_init
+ *
+ * Create dynamic set of StringInfo.
+ */
+static
+StringSet *
+string_set_init(void)
+{
+/* Initial allocation size of str_set */
+#define STRING_SET_ALLOC_SIZE 1024
+
+ StringSet *string_set;
+ Size set_size;
+
+ string_set = palloc0(sizeof(StringSet));
+ string_set->set_index = 0;
+ set_size = STRING_SET_ALLOC_SIZE;
+ string_set->str_set = palloc(set_size * sizeof(StringInfo));
+ string_set->info = palloc0(set_size * sizeof(int));
+ string_set->set_size = set_size;
+
+ return string_set;
+}
+
+/*
+ * string_set_add
+ *
+ * Add StringInfo str to StringSet string_set.
+ */
+static
+void
+string_set_add(StringSet *string_set, StringInfo str, int info)
+{
+ Size set_size;
+ Size old_set_size;
+
+ set_size = string_set->set_size;
+ if (string_set->set_index >= set_size)
+ {
+ old_set_size = set_size;
+ set_size *= 2;
+ string_set->str_set = repalloc(string_set->str_set,
+ set_size * sizeof(StringInfo));
+ string_set->info = repalloc0(string_set->info,
+ old_set_size * sizeof(int),
+ set_size * sizeof(int));
+ string_set->set_size = set_size;
+ }
+
+ string_set->info[string_set->set_index] = info;
+ string_set->str_set[string_set->set_index++] = str;
+
+ return;
+}
+
+/*
+ * string_set_get
+ *
+ * Returns StringInfo specified by index.
+ * If there's no data yet, returns NULL.
+ */
+static
+StringInfo
+string_set_get(StringSet *string_set, int index, int *info)
+{
+ *info = 0;
+
+ /* no data? */
+ if (index == 0 && string_set->set_index == 0)
+ return NULL;
+
+ if (index < 0 || index >= string_set->set_index)
+ elog(ERROR, "invalid index: %d", index);
+
+ *info = string_set->info[index];
+
+ return string_set->str_set[index];
+}
+
+/*
+ * string_set_get_size
+ *
+ * Returns the size of StringSet.
+ */
+static
+int
+string_set_get_size(StringSet *string_set)
+{
+ return string_set->set_index;
+}
+
+/*
+ * string_set_discard
+ * Discard StringSet.
+ * All memory including StringSet itself is freed.
+ */
+static
+void
+string_set_discard(StringSet *string_set)
+{
+ int i;
+
+ for (i = 0; i < string_set->set_index; i++)
+ {
+ StringInfo str = string_set->str_set[i];
+
+ if (str)
+ destroyStringInfo(str);
+ }
+ pfree(string_set->info);
+ pfree(string_set->str_set);
+ pfree(string_set);
+}
+
+/*
+ * variable_pos_init
+ *
+ * Create and initialize variable postion structure
+ */
+static
+VariablePos *
+variable_pos_init(void)
+{
+ VariablePos *variable_pos;
+
+ variable_pos = palloc(sizeof(VariablePos) * NUM_ALPHABETS);
+ MemSet(variable_pos, -1, sizeof(VariablePos) * NUM_ALPHABETS);
+ return variable_pos;
+}
+
+/*
+ * variable_pos_register
+ *
+ * Register pattern variable whose initial is initial into postion index.
+ * pos is position of initial.
+ * If pos is already registered, register it at next empty slot.
+ */
+static
+void
+variable_pos_register(VariablePos *variable_pos, char initial, int pos)
+{
+ int index = initial - 'a';
+ int slot;
+ int i;
+
+ if (pos < 0 || pos > NUM_ALPHABETS)
+ elog(ERROR, "initial is not valid char: %c", initial);
+
+ for (i = 0; i < NUM_ALPHABETS; i++)
+ {
+ slot = variable_pos[index].pos[i];
+ if (slot < 0)
+ {
+ /* empty slot found */
+ variable_pos[index].pos[i] = pos;
+ return;
+ }
+ }
+ elog(ERROR, "no empty slot for initial: %c", initial);
+}
+
+/*
+ * variable_pos_compare
+ *
+ * Returns true if initial1 can be followed by initial2
+ */
+static
+bool
+variable_pos_compare(VariablePos *variable_pos, char initial1, char initial2)
+{
+ int index1,
+ index2;
+ int pos1,
+ pos2;
+
+ for (index1 = 0;; index1++)
+ {
+ pos1 = variable_pos_fetch(variable_pos, initial1, index1);
+ if (pos1 < 0)
+ break;
+
+ for (index2 = 0;; index2++)
+ {
+ pos2 = variable_pos_fetch(variable_pos, initial2, index2);
+ if (pos2 < 0)
+ break;
+ if (pos1 <= pos2)
+ return true;
+ }
+ }
+ return false;
+}
+
+/*
+ * variable_pos_fetch
+ *
+ * Fetch position of pattern variable whose initial is initial, and whose index
+ * is index. If no postion was registered by initial, index, returns -1.
+ */
+static
+int
+variable_pos_fetch(VariablePos *variable_pos, char initial, int index)
+{
+ int pos = initial - 'a';
+
+ if (pos < 0 || pos > NUM_ALPHABETS)
+ elog(ERROR, "initial is not valid char: %c", initial);
+
+ if (index < 0 || index > NUM_ALPHABETS)
+ elog(ERROR, "index is not valid: %d", index);
+
+ return variable_pos[pos].pos[index];
+}
+
+/*
+ * variable_pos_build
+ *
+ * Build VariablePos structure and return it.
+ */
+static
+VariablePos *
+variable_pos_build(WindowAggState *winstate)
+{
+ VariablePos *variable_pos;
+ StringInfo pattern_str;
+ int initial_index = 0;
+ ListCell *lc1,
+ *lc2;
+
+ variable_pos = winstate->variable_pos = variable_pos_init();
+ pattern_str = winstate->pattern_str = makeStringInfo();
+ appendStringInfoChar(pattern_str, '^');
+
+ forboth(lc1, winstate->patternVariableList,
+ lc2, winstate->patternRegexpList)
+ {
+ char *vname = strVal(lfirst(lc1));
+ char *quantifier = strVal(lfirst(lc2));
+ char initial;
+
+ initial = pattern_initial(winstate, vname);
+ Assert(initial != 0);
+ appendStringInfoChar(pattern_str, initial);
+ if (quantifier[0])
+ appendStringInfoChar(pattern_str, quantifier[0]);
+
+ /*
+ * Register the initial at initial_index. If the initial appears more
+ * than once, all of it's initial_index will be recorded. This could
+ * happen if a pattern variable appears in the PATTERN clause more
+ * than once like "UP DOWN UP" "UP UP UP".
+ */
+ variable_pos_register(variable_pos, initial, initial_index);
+
+ initial_index++;
+ }
+
+ return variable_pos;
+}
diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c
index bb35f3bc4a9..432d6c1b0a3 100644
--- a/src/backend/utils/adt/windowfuncs.c
+++ b/src/backend/utils/adt/windowfuncs.c
@@ -37,11 +37,19 @@ typedef struct
int64 remainder; /* (total rows) % (bucket num) */
} ntile_context;
+/*
+ * rpr process information.
+ * Used for AFTER MATCH SKIP PAST LAST ROW
+ */
+typedef struct SkipContext
+{
+ int64 pos; /* last row absolute position */
+} SkipContext;
+
static bool rank_up(WindowObject winobj);
static Datum leadlag_common(FunctionCallInfo fcinfo,
bool forward, bool withoffset, bool withdefault);
-
/*
* utility routine for *_rank functions.
*/
@@ -674,7 +682,7 @@ window_last_value(PG_FUNCTION_ARGS)
bool isnull;
result = WinGetFuncArgInFrame(winobj, 0,
- 0, WINDOW_SEEK_TAIL, true,
+ 0, WINDOW_SEEK_TAIL, false,
&isnull, NULL);
if (isnull)
PG_RETURN_NULL();
@@ -714,3 +722,25 @@ window_nth_value(PG_FUNCTION_ARGS)
PG_RETURN_DATUM(result);
}
+
+/*
+ * prev
+ * Dummy function to invoke RPR's navigation operator "PREV".
+ * This is *not* a window function.
+ */
+Datum
+window_prev(PG_FUNCTION_ARGS)
+{
+ PG_RETURN_DATUM(PG_GETARG_DATUM(0));
+}
+
+/*
+ * next
+ * Dummy function to invoke RPR's navigation operation "NEXT".
+ * This is *not* a window function.
+ */
+Datum
+window_next(PG_FUNCTION_ARGS)
+{
+ PG_RETURN_DATUM(PG_GETARG_DATUM(0));
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 01eba3b5a19..a946862dd7d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10829,6 +10829,12 @@
{ oid => '3114', descr => 'fetch the Nth row value',
proname => 'nth_value', prokind => 'w', prorettype => 'anyelement',
proargtypes => 'anyelement int4', prosrc => 'window_nth_value' },
+{ oid => '8126', descr => 'previous value',
+ proname => 'prev', provolatile => 's', prorettype => 'anyelement',
+ proargtypes => 'anyelement', prosrc => 'window_prev' },
+{ oid => '8127', descr => 'next value',
+ proname => 'next', provolatile => 's', prorettype => 'anyelement',
+ proargtypes => 'anyelement', prosrc => 'window_next' },
# functions for range types
{ oid => '3832', descr => 'I/O',
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 3a920cc7d17..68e406a79b4 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -2619,6 +2619,37 @@ typedef enum WindowAggStatus
* tuples during spool */
} WindowAggStatus;
+#define RF_NOT_DETERMINED 0
+#define RF_FRAME_HEAD 1
+#define RF_SKIPPED 2
+#define RF_UNMATCHED 3
+
+/*
+ * Allowed PATTERN variables positions.
+ * Used in RPR.
+ *
+ * pos represents the pattern variable defined order in DEFINE caluase. For
+ * example. "DEFINE START..., UP..., DOWN ..." and "PATTERN START UP DOWN UP"
+ * will create:
+ * VariablePos[0].pos[0] = 0; START
+ * VariablePos[1].pos[0] = 1; UP
+ * VariablePos[1].pos[1] = 3; UP
+ * VariablePos[2].pos[0] = 2; DOWN
+ *
+ * Note that UP has two pos because UP appears in PATTERN twice.
+ *
+ * By using this strucrture, we can know which pattern variable can be followed
+ * by which pattern variable(s). For example, START can be followed by UP and
+ * DOWN since START's pos is 0, and UP's pos is 1 or 3, DOWN's pos is 2.
+ * DOWN can be followed by UP since UP's pos is either 1 or 3.
+ *
+ */
+#define NUM_ALPHABETS 26 /* we allow [a-z] variable initials */
+typedef struct VariablePos
+{
+ int pos[NUM_ALPHABETS]; /* postions in PATTERN */
+} VariablePos;
+
typedef struct WindowAggState
{
ScanState ss; /* its first field is NodeTag */
@@ -2678,6 +2709,21 @@ typedef struct WindowAggState
int64 groupheadpos; /* current row's peer group head position */
int64 grouptailpos; /* " " " " tail position (group end+1) */
+ /* these fields are used in Row pattern recognition: */
+ RPSkipTo rpSkipTo; /* Row Pattern Skip To type */
+ List *patternVariableList; /* list of row pattern variables names
+ * (list of String) */
+ List *patternRegexpList; /* list of row pattern regular expressions
+ * ('+' or ''. list of String) */
+ List *defineVariableList; /* list of row pattern definition
+ * variables (list of String) */
+ List *defineClauseList; /* expression for row pattern definition
+ * search conditions ExprState list */
+ List *defineInitial; /* list of row pattern definition variable
+ * initials (list of String) */
+ VariablePos *variable_pos; /* list of pattern variable positions */
+ StringInfo pattern_str; /* PATTERN initials */
+
MemoryContext partcontext; /* context for partition-lifespan data */
MemoryContext aggcontext; /* shared context for aggregate working data */
MemoryContext curaggcontext; /* current aggregate's working data */
@@ -2705,6 +2751,18 @@ typedef struct WindowAggState
TupleTableSlot *agg_row_slot;
TupleTableSlot *temp_slot_1;
TupleTableSlot *temp_slot_2;
+
+ /* temporary slots for RPR */
+ TupleTableSlot *prev_slot; /* PREV row navigation operator */
+ TupleTableSlot *next_slot; /* NEXT row navigation operator */
+ TupleTableSlot *null_slot; /* all NULL slot */
+
+ /*
+ * Each byte corresponds to a row positioned at absolute its pos in
+ * partition. See above definition for RF_*
+ */
+ char *reduced_frame_map;
+ int64 alloc_sz; /* size of the map */
} WindowAggState;
/* ----------------
--
2.43.0
[application/octet-stream] v33-0006-Row-pattern-recognition-patch-docs.patch (10.5K, ../[email protected]/7-v33-0006-Row-pattern-recognition-patch-docs.patch)
download | inline diff:
From d9c0c763f8371159dbaef5024b06ad4f0f5ec961 Mon Sep 17 00:00:00 2001
From: Tatsuo Ishii <[email protected]>
Date: Wed, 24 Sep 2025 19:18:37 +0900
Subject: [PATCH v33 6/8] Row pattern recognition patch (docs).
---
doc/src/sgml/advanced.sgml | 81 ++++++++++++++++++++++++++++++
doc/src/sgml/func/func-window.sgml | 53 +++++++++++++++++++
doc/src/sgml/ref/select.sgml | 49 ++++++++++++++++--
3 files changed, 180 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml
index e15a3323dfb..516ce28583d 100644
--- a/doc/src/sgml/advanced.sgml
+++ b/doc/src/sgml/advanced.sgml
@@ -540,6 +540,87 @@ WHERE pos < 3;
two rows for each department).
</para>
+ <para>
+ Row pattern common syntax can be used to perform row pattern recognition
+ in a query. The row pattern common syntax includes two sub
+ clauses: <literal>DEFINE</literal>
+ and <literal>PATTERN</literal>. <literal>DEFINE</literal> defines
+ definition variables along with an expression. The expression must be a
+ logical expression, which means it must
+ return <literal>TRUE</literal>, <literal>FALSE</literal>
+ or <literal>NULL</literal>. The expression may comprise column references
+ and functions. Window functions, aggregate functions and subqueries are
+ not allowed. An example of <literal>DEFINE</literal> is as follows.
+
+<programlisting>
+DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+</programlisting>
+
+ Note that <function>PREV</function> returns the price column in the
+ previous row if it's called in a context of row pattern recognition. Thus
+ in the second line the definition variable "UP" is <literal>TRUE</literal>
+ when the price column in the current row is greater than the price column
+ in the previous row. Likewise, "DOWN" is <literal>TRUE</literal> when the
+ price column in the current row is lower than the price column in the
+ previous row.
+ </para>
+ <para>
+ Once <literal>DEFINE</literal> exists, <literal>PATTERN</literal> can be
+ used. <literal>PATTERN</literal> defines a sequence of rows that satisfies
+ conditions defined in the <literal>DEFINE</literal> clause. For example
+ following <literal>PATTERN</literal> defines a sequence of rows starting
+ with the a row satisfying "LOWPRICE", then one or more rows satisfying
+ "UP" and finally one or more rows satisfying "DOWN". Note that "+" means
+ one or more matches. Also you can use "*", which means zero or more
+ matches. If a sequence of rows which satisfies the PATTERN is found, in
+ the starting row all columns or functions are shown in the target
+ list. Note that aggregations only look into the matched rows, rather than
+ the whole frame. On the second or subsequent rows all window functions are
+ shown as NULL. Aggregates are NULL or 0 depending on its aggregation
+ definition. A count() aggregate shows 0. For rows that do not match on the
+ PATTERN, columns are shown AS NULL too. Example of
+ a <literal>SELECT</literal> using the <literal>DEFINE</literal>
+ and <literal>PATTERN</literal> clause is as follows.
+
+<programlisting>
+SELECT company, tdate, price,
+ first_value(price) OVER w,
+ max(price) OVER w,
+ count(price) OVER w
+FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ INITIAL
+ PATTERN (LOWPRICE UP+ DOWN+)
+ DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+</programlisting>
+<screen>
+ company | tdate | price | first_value | max | count
+----------+------------+-------+-------------+-----+-------
+ company1 | 2023-07-01 | 100 | 100 | 200 | 4
+ company1 | 2023-07-02 | 200 | | | 0
+ company1 | 2023-07-03 | 150 | | | 0
+ company1 | 2023-07-04 | 140 | | | 0
+ company1 | 2023-07-05 | 150 | | | 0
+ company1 | 2023-07-06 | 90 | 90 | 130 | 4
+ company1 | 2023-07-07 | 110 | | | 0
+ company1 | 2023-07-08 | 130 | | | 0
+ company1 | 2023-07-09 | 120 | | | 0
+ company1 | 2023-07-10 | 130 | | | 0
+(10 rows)
+</screen>
+ </para>
+
<para>
When a query involves multiple window functions, it is possible to write
out each one with a separate <literal>OVER</literal> clause, but this is
diff --git a/doc/src/sgml/func/func-window.sgml b/doc/src/sgml/func/func-window.sgml
index cce0165b952..c9afb4fcb88 100644
--- a/doc/src/sgml/func/func-window.sgml
+++ b/doc/src/sgml/func/func-window.sgml
@@ -265,6 +265,59 @@
Other frame specifications can be used to obtain other effects.
</para>
+ <para>
+ Row pattern recognition navigation functions are listed in
+ <xref linkend="functions-rpr-navigation-table"/>. These functions
+ can be used to describe DEFINE clause of Row pattern recognition.
+ </para>
+
+ <table id="functions-rpr-navigation-table">
+ <title>Row Pattern Navigation Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>prev</primary>
+ </indexterm>
+ <function>prev</function> ( <parameter>value</parameter> <type>anyelement</type> )
+ <returnvalue>anyelement</returnvalue>
+ </para>
+ <para>
+ Returns the column value at the previous row;
+ returns NULL if there is no previous row in the window frame.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>next</primary>
+ </indexterm>
+ <function>next</function> ( <parameter>value</parameter> <type>anyelement</type> )
+ <returnvalue>anyelement</returnvalue>
+ </para>
+ <para>
+ Returns the column value at the next row;
+ returns NULL if there is no next row in the window frame.
+ </para></entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </table>
+
<note>
<para>
The SQL standard defines a <literal>RESPECT NULLS</literal> or
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index d7089eac0be..5833dc70b43 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -927,6 +927,7 @@ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceabl
[ PARTITION BY <replaceable class="parameter">expression</replaceable> [, ...] ]
[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ <replaceable class="parameter">frame_clause</replaceable> ]
+[ <replaceable class="parameter">row_pattern_common_syntax</replaceable> ]
</synopsis>
</para>
@@ -969,8 +970,8 @@ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceabl
The <replaceable class="parameter">frame_clause</replaceable> can be one of
<synopsis>
-{ RANGE | ROWS | GROUPS } <replaceable>frame_start</replaceable> [ <replaceable>frame_exclusion</replaceable> ]
-{ RANGE | ROWS | GROUPS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable>frame_exclusion</replaceable> ]
+{ RANGE | ROWS | GROUPS } <replaceable>frame_start</replaceable> [ <replaceable>frame_exclusion</replaceable> ] [row_pattern_common_syntax]
+{ RANGE | ROWS | GROUPS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable>frame_exclusion</replaceable> ] [row_pattern_common_syntax]
</synopsis>
where <replaceable>frame_start</replaceable>
@@ -1077,9 +1078,51 @@ EXCLUDE NO OTHERS
a given peer group will be in the frame or excluded from it.
</para>
+ <para>
+ The
+ optional <replaceable class="parameter">row_pattern_common_syntax</replaceable>
+ defines the <firstterm>row pattern recognition condition</firstterm> for
+ this
+ window. <replaceable class="parameter">row_pattern_common_syntax</replaceable>
+ includes following subclauses.
+
+<synopsis>
+[ AFTER MATCH SKIP PAST LAST ROW | AFTER MATCH SKIP TO NEXT ROW ]
+PATTERN <replaceable class="parameter">pattern_variable_name</replaceable>[+] [, ...]
+DEFINE <replaceable class="parameter">definition_varible_name</replaceable> AS <replaceable class="parameter">expression</replaceable> [, ...]
+</synopsis>
+ <literal>AFTER MATCH SKIP PAST LAST ROW</literal> or <literal>AFTER MATCH
+ SKIP TO NEXT ROW</literal> controls how to proceed to next row position
+ after a match found. With <literal>AFTER MATCH SKIP PAST LAST
+ ROW</literal> (the default) next row position is next to the last row of
+ previous match. On the other hand, with <literal>AFTER MATCH SKIP TO NEXT
+ ROW</literal> next row position is always next to the last row of previous
+ match. <literal>DEFINE</literal> defines definition variables along with a
+ boolean expression. <literal>PATTERN</literal> defines a sequence of rows
+ that satisfies certain conditions using variables defined
+ in <literal>DEFINE</literal> clause. If the variable is not defined in
+ the <literal>DEFINE</literal> clause, it is implicitly assumed following
+ is defined in the <literal>DEFINE</literal> clause.
+
+<synopsis>
+<literal>variable_name</literal> AS TRUE
+</synopsis>
+
+ Note that the maximu number of variables defined
+ in <literal>DEFINE</literal> clause is 26.
+ </para>
+
+ <para>
+ The SQL standard defines more subclauses: <literal>MEASURES</literal>
+ and <literal>SUBSET</literal>. They are not currently supported
+ in <productname>PostgreSQL</productname>. Also in the standard there are
+ more variations in <literal>AFTER MATCH</literal> clause.
+ </para>
+
<para>
The purpose of a <literal>WINDOW</literal> clause is to specify the
- behavior of <firstterm>window functions</firstterm> appearing in the query's
+ behavior of <firstterm>window functions</firstterm> appearing in the
+ query's
<link linkend="sql-select-list"><command>SELECT</command> list</link> or
<link linkend="sql-orderby"><literal>ORDER BY</literal></link> clause.
These functions
--
2.43.0
[application/octet-stream] v33-0007-Row-pattern-recognition-patch-tests.patch (54.4K, ../[email protected]/8-v33-0007-Row-pattern-recognition-patch-tests.patch)
download | inline diff:
From db87a09d605aac7741a18191fcaaae300083a7f6 Mon Sep 17 00:00:00 2001
From: Tatsuo Ishii <[email protected]>
Date: Wed, 24 Sep 2025 19:18:37 +0900
Subject: [PATCH v33 7/8] Row pattern recognition patch (tests).
---
src/test/regress/expected/rpr.out | 979 +++++++++++++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/rpr.sql | 486 ++++++++++++++
3 files changed, 1466 insertions(+), 1 deletion(-)
create mode 100644 src/test/regress/expected/rpr.out
create mode 100644 src/test/regress/sql/rpr.sql
diff --git a/src/test/regress/expected/rpr.out b/src/test/regress/expected/rpr.out
new file mode 100644
index 00000000000..59cfed180e7
--- /dev/null
+++ b/src/test/regress/expected/rpr.out
@@ -0,0 +1,979 @@
+--
+-- Test for row pattern definition clause
+--
+CREATE TEMP TABLE stock (
+ company TEXT,
+ tdate DATE,
+ price INTEGER
+);
+INSERT INTO stock VALUES ('company1', '2023-07-01', 100);
+INSERT INTO stock VALUES ('company1', '2023-07-02', 200);
+INSERT INTO stock VALUES ('company1', '2023-07-03', 150);
+INSERT INTO stock VALUES ('company1', '2023-07-04', 140);
+INSERT INTO stock VALUES ('company1', '2023-07-05', 150);
+INSERT INTO stock VALUES ('company1', '2023-07-06', 90);
+INSERT INTO stock VALUES ('company1', '2023-07-07', 110);
+INSERT INTO stock VALUES ('company1', '2023-07-08', 130);
+INSERT INTO stock VALUES ('company1', '2023-07-09', 120);
+INSERT INTO stock VALUES ('company1', '2023-07-10', 130);
+INSERT INTO stock VALUES ('company2', '2023-07-01', 50);
+INSERT INTO stock VALUES ('company2', '2023-07-02', 2000);
+INSERT INTO stock VALUES ('company2', '2023-07-03', 1500);
+INSERT INTO stock VALUES ('company2', '2023-07-04', 1400);
+INSERT INTO stock VALUES ('company2', '2023-07-05', 1500);
+INSERT INTO stock VALUES ('company2', '2023-07-06', 60);
+INSERT INTO stock VALUES ('company2', '2023-07-07', 1100);
+INSERT INTO stock VALUES ('company2', '2023-07-08', 1300);
+INSERT INTO stock VALUES ('company2', '2023-07-09', 1200);
+INSERT INTO stock VALUES ('company2', '2023-07-10', 1300);
+SELECT * FROM stock;
+ company | tdate | price
+----------+------------+-------
+ company1 | 07-01-2023 | 100
+ company1 | 07-02-2023 | 200
+ company1 | 07-03-2023 | 150
+ company1 | 07-04-2023 | 140
+ company1 | 07-05-2023 | 150
+ company1 | 07-06-2023 | 90
+ company1 | 07-07-2023 | 110
+ company1 | 07-08-2023 | 130
+ company1 | 07-09-2023 | 120
+ company1 | 07-10-2023 | 130
+ company2 | 07-01-2023 | 50
+ company2 | 07-02-2023 | 2000
+ company2 | 07-03-2023 | 1500
+ company2 | 07-04-2023 | 1400
+ company2 | 07-05-2023 | 1500
+ company2 | 07-06-2023 | 60
+ company2 | 07-07-2023 | 1100
+ company2 | 07-08-2023 | 1300
+ company2 | 07-09-2023 | 1200
+ company2 | 07-10-2023 | 1300
+(20 rows)
+
+-- basic test using PREV
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w,
+ nth_value(tdate, 2) OVER w AS nth_second
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ company | tdate | price | first_value | last_value | nth_second
+----------+------------+-------+-------------+------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 140 | 07-02-2023
+ company1 | 07-02-2023 | 200 | | |
+ company1 | 07-03-2023 | 150 | | |
+ company1 | 07-04-2023 | 140 | | |
+ company1 | 07-05-2023 | 150 | | |
+ company1 | 07-06-2023 | 90 | 90 | 120 | 07-07-2023
+ company1 | 07-07-2023 | 110 | | |
+ company1 | 07-08-2023 | 130 | | |
+ company1 | 07-09-2023 | 120 | | |
+ company1 | 07-10-2023 | 130 | | |
+ company2 | 07-01-2023 | 50 | 50 | 1400 | 07-02-2023
+ company2 | 07-02-2023 | 2000 | | |
+ company2 | 07-03-2023 | 1500 | | |
+ company2 | 07-04-2023 | 1400 | | |
+ company2 | 07-05-2023 | 1500 | | |
+ company2 | 07-06-2023 | 60 | 60 | 1200 | 07-07-2023
+ company2 | 07-07-2023 | 1100 | | |
+ company2 | 07-08-2023 | 1300 | | |
+ company2 | 07-09-2023 | 1200 | | |
+ company2 | 07-10-2023 | 1300 | | |
+(20 rows)
+
+-- basic test using PREV. UP appears twice
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w,
+ nth_value(tdate, 2) OVER w AS nth_second
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+ UP+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ company | tdate | price | first_value | last_value | nth_second
+----------+------------+-------+-------------+------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 150 | 07-02-2023
+ company1 | 07-02-2023 | 200 | | |
+ company1 | 07-03-2023 | 150 | | |
+ company1 | 07-04-2023 | 140 | | |
+ company1 | 07-05-2023 | 150 | | |
+ company1 | 07-06-2023 | 90 | 90 | 130 | 07-07-2023
+ company1 | 07-07-2023 | 110 | | |
+ company1 | 07-08-2023 | 130 | | |
+ company1 | 07-09-2023 | 120 | | |
+ company1 | 07-10-2023 | 130 | | |
+ company2 | 07-01-2023 | 50 | 50 | 1500 | 07-02-2023
+ company2 | 07-02-2023 | 2000 | | |
+ company2 | 07-03-2023 | 1500 | | |
+ company2 | 07-04-2023 | 1400 | | |
+ company2 | 07-05-2023 | 1500 | | |
+ company2 | 07-06-2023 | 60 | 60 | 1300 | 07-07-2023
+ company2 | 07-07-2023 | 1100 | | |
+ company2 | 07-08-2023 | 1300 | | |
+ company2 | 07-09-2023 | 1200 | | |
+ company2 | 07-10-2023 | 1300 | | |
+(20 rows)
+
+-- basic test using PREV. Use '*'
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w,
+ nth_value(tdate, 2) OVER w AS nth_second
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP* DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ company | tdate | price | first_value | last_value | nth_second
+----------+------------+-------+-------------+------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 140 | 07-02-2023
+ company1 | 07-02-2023 | 200 | | |
+ company1 | 07-03-2023 | 150 | | |
+ company1 | 07-04-2023 | 140 | | |
+ company1 | 07-05-2023 | 150 | 150 | 90 | 07-06-2023
+ company1 | 07-06-2023 | 90 | | |
+ company1 | 07-07-2023 | 110 | 110 | 120 | 07-08-2023
+ company1 | 07-08-2023 | 130 | | |
+ company1 | 07-09-2023 | 120 | | |
+ company1 | 07-10-2023 | 130 | | |
+ company2 | 07-01-2023 | 50 | 50 | 1400 | 07-02-2023
+ company2 | 07-02-2023 | 2000 | | |
+ company2 | 07-03-2023 | 1500 | | |
+ company2 | 07-04-2023 | 1400 | | |
+ company2 | 07-05-2023 | 1500 | 1500 | 60 | 07-06-2023
+ company2 | 07-06-2023 | 60 | | |
+ company2 | 07-07-2023 | 1100 | 1100 | 1200 | 07-08-2023
+ company2 | 07-08-2023 | 1300 | | |
+ company2 | 07-09-2023 | 1200 | | |
+ company2 | 07-10-2023 | 1300 | | |
+(20 rows)
+
+-- basic test with none greedy pattern
+SELECT company, tdate, price, count(*) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A A A)
+ DEFINE
+ A AS price >= 140 AND price <= 150
+);
+ company | tdate | price | count
+----------+------------+-------+-------
+ company1 | 07-01-2023 | 100 | 0
+ company1 | 07-02-2023 | 200 | 0
+ company1 | 07-03-2023 | 150 | 3
+ company1 | 07-04-2023 | 140 | 0
+ company1 | 07-05-2023 | 150 | 0
+ company1 | 07-06-2023 | 90 | 0
+ company1 | 07-07-2023 | 110 | 0
+ company1 | 07-08-2023 | 130 | 0
+ company1 | 07-09-2023 | 120 | 0
+ company1 | 07-10-2023 | 130 | 0
+ company2 | 07-01-2023 | 50 | 0
+ company2 | 07-02-2023 | 2000 | 0
+ company2 | 07-03-2023 | 1500 | 0
+ company2 | 07-04-2023 | 1400 | 0
+ company2 | 07-05-2023 | 1500 | 0
+ company2 | 07-06-2023 | 60 | 0
+ company2 | 07-07-2023 | 1100 | 0
+ company2 | 07-08-2023 | 1300 | 0
+ company2 | 07-09-2023 | 1200 | 0
+ company2 | 07-10-2023 | 1300 | 0
+(20 rows)
+
+-- last_value() should remain consistent
+SELECT company, tdate, price, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ company | tdate | price | last_value
+----------+------------+-------+------------
+ company1 | 07-01-2023 | 100 | 140
+ company1 | 07-02-2023 | 200 |
+ company1 | 07-03-2023 | 150 |
+ company1 | 07-04-2023 | 140 |
+ company1 | 07-05-2023 | 150 |
+ company1 | 07-06-2023 | 90 | 120
+ company1 | 07-07-2023 | 110 |
+ company1 | 07-08-2023 | 130 |
+ company1 | 07-09-2023 | 120 |
+ company1 | 07-10-2023 | 130 |
+ company2 | 07-01-2023 | 50 | 1400
+ company2 | 07-02-2023 | 2000 |
+ company2 | 07-03-2023 | 1500 |
+ company2 | 07-04-2023 | 1400 |
+ company2 | 07-05-2023 | 1500 |
+ company2 | 07-06-2023 | 60 | 1200
+ company2 | 07-07-2023 | 1100 |
+ company2 | 07-08-2023 | 1300 |
+ company2 | 07-09-2023 | 1200 |
+ company2 | 07-10-2023 | 1300 |
+(20 rows)
+
+-- omit "START" in DEFINE but it is ok because "START AS TRUE" is
+-- implicitly defined. per spec.
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w,
+ nth_value(tdate, 2) OVER w AS nth_second
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ company | tdate | price | first_value | last_value | nth_second
+----------+------------+-------+-------------+------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 140 | 07-02-2023
+ company1 | 07-02-2023 | 200 | | |
+ company1 | 07-03-2023 | 150 | | |
+ company1 | 07-04-2023 | 140 | | |
+ company1 | 07-05-2023 | 150 | | |
+ company1 | 07-06-2023 | 90 | 90 | 120 | 07-07-2023
+ company1 | 07-07-2023 | 110 | | |
+ company1 | 07-08-2023 | 130 | | |
+ company1 | 07-09-2023 | 120 | | |
+ company1 | 07-10-2023 | 130 | | |
+ company2 | 07-01-2023 | 50 | 50 | 1400 | 07-02-2023
+ company2 | 07-02-2023 | 2000 | | |
+ company2 | 07-03-2023 | 1500 | | |
+ company2 | 07-04-2023 | 1400 | | |
+ company2 | 07-05-2023 | 1500 | | |
+ company2 | 07-06-2023 | 60 | 60 | 1200 | 07-07-2023
+ company2 | 07-07-2023 | 1100 | | |
+ company2 | 07-08-2023 | 1300 | | |
+ company2 | 07-09-2023 | 1200 | | |
+ company2 | 07-10-2023 | 1300 | | |
+(20 rows)
+
+-- the first row start with less than or equal to 100
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (LOWPRICE UP+ DOWN+)
+ DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 140
+ company1 | 07-02-2023 | 200 | |
+ company1 | 07-03-2023 | 150 | |
+ company1 | 07-04-2023 | 140 | |
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | 90 | 120
+ company1 | 07-07-2023 | 110 | |
+ company1 | 07-08-2023 | 130 | |
+ company1 | 07-09-2023 | 120 | |
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | 50 | 1400
+ company2 | 07-02-2023 | 2000 | |
+ company2 | 07-03-2023 | 1500 | |
+ company2 | 07-04-2023 | 1400 | |
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | 60 | 1200
+ company2 | 07-07-2023 | 1100 | |
+ company2 | 07-08-2023 | 1300 | |
+ company2 | 07-09-2023 | 1200 | |
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+-- second row raises 120%
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (LOWPRICE UP+ DOWN+)
+ DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price) * 1.2,
+ DOWN AS price < PREV(price)
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 140
+ company1 | 07-02-2023 | 200 | |
+ company1 | 07-03-2023 | 150 | |
+ company1 | 07-04-2023 | 140 | |
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | |
+ company1 | 07-07-2023 | 110 | |
+ company1 | 07-08-2023 | 130 | |
+ company1 | 07-09-2023 | 120 | |
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | 50 | 1400
+ company2 | 07-02-2023 | 2000 | |
+ company2 | 07-03-2023 | 1500 | |
+ company2 | 07-04-2023 | 1400 | |
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | |
+ company2 | 07-07-2023 | 1100 | |
+ company2 | 07-08-2023 | 1300 | |
+ company2 | 07-09-2023 | 1200 | |
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+-- using NEXT
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UPDOWN)
+ DEFINE
+ START AS TRUE,
+ UPDOWN AS price > PREV(price) AND price > NEXT(price)
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 200
+ company1 | 07-02-2023 | 200 | |
+ company1 | 07-03-2023 | 150 | |
+ company1 | 07-04-2023 | 140 | 140 | 150
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | |
+ company1 | 07-07-2023 | 110 | 110 | 130
+ company1 | 07-08-2023 | 130 | |
+ company1 | 07-09-2023 | 120 | |
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | 50 | 2000
+ company2 | 07-02-2023 | 2000 | |
+ company2 | 07-03-2023 | 1500 | |
+ company2 | 07-04-2023 | 1400 | 1400 | 1500
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | |
+ company2 | 07-07-2023 | 1100 | 1100 | 1300
+ company2 | 07-08-2023 | 1300 | |
+ company2 | 07-09-2023 | 1200 | |
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+-- using AFTER MATCH SKIP TO NEXT ROW
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ INITIAL
+ PATTERN (START UPDOWN)
+ DEFINE
+ START AS TRUE,
+ UPDOWN AS price > PREV(price) AND price > NEXT(price)
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 200
+ company1 | 07-02-2023 | 200 | |
+ company1 | 07-03-2023 | 150 | |
+ company1 | 07-04-2023 | 140 | 140 | 150
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | |
+ company1 | 07-07-2023 | 110 | 110 | 130
+ company1 | 07-08-2023 | 130 | |
+ company1 | 07-09-2023 | 120 | |
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | 50 | 2000
+ company2 | 07-02-2023 | 2000 | |
+ company2 | 07-03-2023 | 1500 | |
+ company2 | 07-04-2023 | 1400 | 1400 | 1500
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | |
+ company2 | 07-07-2023 | 1100 | 1100 | 1300
+ company2 | 07-08-2023 | 1300 | |
+ company2 | 07-09-2023 | 1200 | |
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+-- match everything
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ INITIAL
+ PATTERN (A+)
+ DEFINE
+ A AS TRUE
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 130
+ company1 | 07-02-2023 | 200 | |
+ company1 | 07-03-2023 | 150 | |
+ company1 | 07-04-2023 | 140 | |
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | |
+ company1 | 07-07-2023 | 110 | |
+ company1 | 07-08-2023 | 130 | |
+ company1 | 07-09-2023 | 120 | |
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | 50 | 1300
+ company2 | 07-02-2023 | 2000 | |
+ company2 | 07-03-2023 | 1500 | |
+ company2 | 07-04-2023 | 1400 | |
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | |
+ company2 | 07-07-2023 | 1100 | |
+ company2 | 07-08-2023 | 1300 | |
+ company2 | 07-09-2023 | 1200 | |
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+-- backtracking with reclassification of rows
+-- using AFTER MATCH SKIP PAST LAST ROW
+SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ INITIAL
+ PATTERN (A+ B+)
+ DEFINE
+ A AS price > 100,
+ B AS price > 100
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | |
+ company1 | 07-02-2023 | 200 | 07-02-2023 | 07-05-2023
+ company1 | 07-03-2023 | 150 | |
+ company1 | 07-04-2023 | 140 | |
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | |
+ company1 | 07-07-2023 | 110 | 07-07-2023 | 07-10-2023
+ company1 | 07-08-2023 | 130 | |
+ company1 | 07-09-2023 | 120 | |
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | |
+ company2 | 07-02-2023 | 2000 | 07-02-2023 | 07-05-2023
+ company2 | 07-03-2023 | 1500 | |
+ company2 | 07-04-2023 | 1400 | |
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | |
+ company2 | 07-07-2023 | 1100 | 07-07-2023 | 07-10-2023
+ company2 | 07-08-2023 | 1300 | |
+ company2 | 07-09-2023 | 1200 | |
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+-- backtracking with reclassification of rows
+-- using AFTER MATCH SKIP TO NEXT ROW
+SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ INITIAL
+ PATTERN (A+ B+)
+ DEFINE
+ A AS price > 100,
+ B AS price > 100
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | |
+ company1 | 07-02-2023 | 200 | 07-02-2023 | 07-05-2023
+ company1 | 07-03-2023 | 150 | 07-03-2023 | 07-05-2023
+ company1 | 07-04-2023 | 140 | 07-04-2023 | 07-05-2023
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | |
+ company1 | 07-07-2023 | 110 | 07-07-2023 | 07-10-2023
+ company1 | 07-08-2023 | 130 | 07-08-2023 | 07-10-2023
+ company1 | 07-09-2023 | 120 | 07-09-2023 | 07-10-2023
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | |
+ company2 | 07-02-2023 | 2000 | 07-02-2023 | 07-05-2023
+ company2 | 07-03-2023 | 1500 | 07-03-2023 | 07-05-2023
+ company2 | 07-04-2023 | 1400 | 07-04-2023 | 07-05-2023
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | |
+ company2 | 07-07-2023 | 1100 | 07-07-2023 | 07-10-2023
+ company2 | 07-08-2023 | 1300 | 07-08-2023 | 07-10-2023
+ company2 | 07-09-2023 | 1200 | 07-09-2023 | 07-10-2023
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+-- ROWS BETWEEN CURRENT ROW AND offset FOLLOWING
+SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w,
+ count(*) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ company | tdate | price | first_value | last_value | count
+----------+------------+-------+-------------+------------+-------
+ company1 | 07-01-2023 | 100 | 07-01-2023 | 07-03-2023 | 3
+ company1 | 07-02-2023 | 200 | | | 0
+ company1 | 07-03-2023 | 150 | | | 0
+ company1 | 07-04-2023 | 140 | 07-04-2023 | 07-06-2023 | 3
+ company1 | 07-05-2023 | 150 | | | 0
+ company1 | 07-06-2023 | 90 | | | 0
+ company1 | 07-07-2023 | 110 | 07-07-2023 | 07-09-2023 | 3
+ company1 | 07-08-2023 | 130 | | | 0
+ company1 | 07-09-2023 | 120 | | | 0
+ company1 | 07-10-2023 | 130 | | | 0
+ company2 | 07-01-2023 | 50 | 07-01-2023 | 07-03-2023 | 3
+ company2 | 07-02-2023 | 2000 | | | 0
+ company2 | 07-03-2023 | 1500 | | | 0
+ company2 | 07-04-2023 | 1400 | 07-04-2023 | 07-06-2023 | 3
+ company2 | 07-05-2023 | 1500 | | | 0
+ company2 | 07-06-2023 | 60 | | | 0
+ company2 | 07-07-2023 | 1100 | 07-07-2023 | 07-09-2023 | 3
+ company2 | 07-08-2023 | 1300 | | | 0
+ company2 | 07-09-2023 | 1200 | | | 0
+ company2 | 07-10-2023 | 1300 | | | 0
+(20 rows)
+
+--
+-- Aggregates
+--
+-- using AFTER MATCH SKIP PAST LAST ROW
+SELECT company, tdate, price,
+ first_value(price) OVER w,
+ last_value(price) OVER w,
+ max(price) OVER w,
+ min(price) OVER w,
+ sum(price) OVER w,
+ avg(price) OVER w,
+ count(price) OVER w
+FROM stock
+WINDOW w AS (
+PARTITION BY company
+ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+AFTER MATCH SKIP PAST LAST ROW
+INITIAL
+PATTERN (START UP+ DOWN+)
+DEFINE
+START AS TRUE,
+UP AS price > PREV(price),
+DOWN AS price < PREV(price)
+);
+ company | tdate | price | first_value | last_value | max | min | sum | avg | count
+----------+------------+-------+-------------+------------+------+-----+------+-----------------------+-------
+ company1 | 07-01-2023 | 100 | 100 | 140 | 200 | 100 | 590 | 147.5000000000000000 | 4
+ company1 | 07-02-2023 | 200 | | | | | | | 0
+ company1 | 07-03-2023 | 150 | | | | | | | 0
+ company1 | 07-04-2023 | 140 | | | | | | | 0
+ company1 | 07-05-2023 | 150 | | | | | | | 0
+ company1 | 07-06-2023 | 90 | 90 | 120 | 130 | 90 | 450 | 112.5000000000000000 | 4
+ company1 | 07-07-2023 | 110 | | | | | | | 0
+ company1 | 07-08-2023 | 130 | | | | | | | 0
+ company1 | 07-09-2023 | 120 | | | | | | | 0
+ company1 | 07-10-2023 | 130 | | | | | | | 0
+ company2 | 07-01-2023 | 50 | 50 | 1400 | 2000 | 50 | 4950 | 1237.5000000000000000 | 4
+ company2 | 07-02-2023 | 2000 | | | | | | | 0
+ company2 | 07-03-2023 | 1500 | | | | | | | 0
+ company2 | 07-04-2023 | 1400 | | | | | | | 0
+ company2 | 07-05-2023 | 1500 | | | | | | | 0
+ company2 | 07-06-2023 | 60 | 60 | 1200 | 1300 | 60 | 3660 | 915.0000000000000000 | 4
+ company2 | 07-07-2023 | 1100 | | | | | | | 0
+ company2 | 07-08-2023 | 1300 | | | | | | | 0
+ company2 | 07-09-2023 | 1200 | | | | | | | 0
+ company2 | 07-10-2023 | 1300 | | | | | | | 0
+(20 rows)
+
+-- using AFTER MATCH SKIP TO NEXT ROW
+SELECT company, tdate, price,
+ first_value(price) OVER w,
+ last_value(price) OVER w,
+ max(price) OVER w,
+ min(price) OVER w,
+ sum(price) OVER w,
+ avg(price) OVER w,
+ count(price) OVER w
+FROM stock
+WINDOW w AS (
+PARTITION BY company
+ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+AFTER MATCH SKIP TO NEXT ROW
+INITIAL
+PATTERN (START UP+ DOWN+)
+DEFINE
+START AS TRUE,
+UP AS price > PREV(price),
+DOWN AS price < PREV(price)
+);
+ company | tdate | price | first_value | last_value | max | min | sum | avg | count
+----------+------------+-------+-------------+------------+------+------+------+-----------------------+-------
+ company1 | 07-01-2023 | 100 | 100 | 140 | 200 | 100 | 590 | 147.5000000000000000 | 4
+ company1 | 07-02-2023 | 200 | | | | | | | 0
+ company1 | 07-03-2023 | 150 | | | | | | | 0
+ company1 | 07-04-2023 | 140 | 140 | 90 | 150 | 90 | 380 | 126.6666666666666667 | 3
+ company1 | 07-05-2023 | 150 | | | | | | | 0
+ company1 | 07-06-2023 | 90 | 90 | 120 | 130 | 90 | 450 | 112.5000000000000000 | 4
+ company1 | 07-07-2023 | 110 | 110 | 120 | 130 | 110 | 360 | 120.0000000000000000 | 3
+ company1 | 07-08-2023 | 130 | | | | | | | 0
+ company1 | 07-09-2023 | 120 | | | | | | | 0
+ company1 | 07-10-2023 | 130 | | | | | | | 0
+ company2 | 07-01-2023 | 50 | 50 | 1400 | 2000 | 50 | 4950 | 1237.5000000000000000 | 4
+ company2 | 07-02-2023 | 2000 | | | | | | | 0
+ company2 | 07-03-2023 | 1500 | | | | | | | 0
+ company2 | 07-04-2023 | 1400 | 1400 | 60 | 1500 | 60 | 2960 | 986.6666666666666667 | 3
+ company2 | 07-05-2023 | 1500 | | | | | | | 0
+ company2 | 07-06-2023 | 60 | 60 | 1200 | 1300 | 60 | 3660 | 915.0000000000000000 | 4
+ company2 | 07-07-2023 | 1100 | 1100 | 1200 | 1300 | 1100 | 3600 | 1200.0000000000000000 | 3
+ company2 | 07-08-2023 | 1300 | | | | | | | 0
+ company2 | 07-09-2023 | 1200 | | | | | | | 0
+ company2 | 07-10-2023 | 1300 | | | | | | | 0
+(20 rows)
+
+-- JOIN case
+CREATE TEMP TABLE t1 (i int, v1 int);
+CREATE TEMP TABLE t2 (j int, v2 int);
+INSERT INTO t1 VALUES(1,10);
+INSERT INTO t1 VALUES(1,11);
+INSERT INTO t1 VALUES(1,12);
+INSERT INTO t2 VALUES(2,10);
+INSERT INTO t2 VALUES(2,11);
+INSERT INTO t2 VALUES(2,12);
+SELECT * FROM t1, t2 WHERE t1.v1 <= 11 AND t2.v2 <= 11;
+ i | v1 | j | v2
+---+----+---+----
+ 1 | 10 | 2 | 10
+ 1 | 10 | 2 | 11
+ 1 | 11 | 2 | 10
+ 1 | 11 | 2 | 11
+(4 rows)
+
+SELECT *, count(*) OVER w FROM t1, t2
+WINDOW w AS (
+ PARTITION BY t1.i
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE
+ A AS v1 <= 11 AND v2 <= 11
+);
+ i | v1 | j | v2 | count
+---+----+---+----+-------
+ 1 | 10 | 2 | 10 | 1
+ 1 | 10 | 2 | 11 | 1
+ 1 | 10 | 2 | 12 | 0
+ 1 | 11 | 2 | 10 | 1
+ 1 | 11 | 2 | 11 | 1
+ 1 | 11 | 2 | 12 | 0
+ 1 | 12 | 2 | 10 | 0
+ 1 | 12 | 2 | 11 | 0
+ 1 | 12 | 2 | 12 | 0
+(9 rows)
+
+-- WITH case
+WITH wstock AS (
+ SELECT * FROM stock WHERE tdate < '2023-07-08'
+)
+SELECT tdate, price,
+first_value(tdate) OVER w,
+count(*) OVER w
+ FROM wstock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ tdate | price | first_value | count
+------------+-------+-------------+-------
+ 07-01-2023 | 100 | 07-01-2023 | 4
+ 07-02-2023 | 200 | | 0
+ 07-03-2023 | 150 | | 0
+ 07-04-2023 | 140 | | 0
+ 07-05-2023 | 150 | | 0
+ 07-06-2023 | 90 | | 0
+ 07-07-2023 | 110 | | 0
+ 07-01-2023 | 50 | 07-01-2023 | 4
+ 07-02-2023 | 2000 | | 0
+ 07-03-2023 | 1500 | | 0
+ 07-04-2023 | 1400 | | 0
+ 07-05-2023 | 1500 | | 0
+ 07-06-2023 | 60 | | 0
+ 07-07-2023 | 1100 | | 0
+(14 rows)
+
+-- PREV has multiple column reference
+CREATE TEMP TABLE rpr1 (id INTEGER, i SERIAL, j INTEGER);
+INSERT INTO rpr1(id, j) SELECT 1, g*2 FROM generate_series(1, 10) AS g;
+SELECT id, i, j, count(*) OVER w
+ FROM rpr1
+ WINDOW w AS (
+ PARTITION BY id
+ ORDER BY i
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ INITIAL
+ PATTERN (START COND+)
+ DEFINE
+ START AS TRUE,
+ COND AS PREV(i + j + 1) < 10
+);
+ id | i | j | count
+----+----+----+-------
+ 1 | 1 | 2 | 3
+ 1 | 2 | 4 | 0
+ 1 | 3 | 6 | 0
+ 1 | 4 | 8 | 0
+ 1 | 5 | 10 | 0
+ 1 | 6 | 12 | 0
+ 1 | 7 | 14 | 0
+ 1 | 8 | 16 | 0
+ 1 | 9 | 18 | 0
+ 1 | 10 | 20 | 0
+(10 rows)
+
+-- Smoke test for larger partitions.
+WITH s AS (
+ SELECT v, count(*) OVER w AS c
+ FROM (SELECT generate_series(1, 5000) v)
+ WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ INITIAL
+ PATTERN ( r+ )
+ DEFINE r AS TRUE
+ )
+)
+-- Should be exactly one long match across all rows.
+SELECT * FROM s WHERE c > 0;
+ v | c
+---+------
+ 1 | 5000
+(1 row)
+
+WITH s AS (
+ SELECT v, count(*) OVER w AS c
+ FROM (SELECT generate_series(1, 5000) v)
+ WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ INITIAL
+ PATTERN ( r )
+ DEFINE r AS TRUE
+ )
+)
+-- Every row should be its own match.
+SELECT count(*) FROM s WHERE c > 0;
+ count
+-------
+ 5000
+(1 row)
+
+-- View and pg_get_viewdef tests.
+CREATE TEMP VIEW v_window AS
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w,
+ nth_value(tdate, 2) OVER w AS nth_second
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+SELECT * FROM v_window;
+ company | tdate | price | first_value | last_value | nth_second
+----------+------------+-------+-------------+------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 140 | 07-02-2023
+ company1 | 07-02-2023 | 200 | | |
+ company1 | 07-03-2023 | 150 | | |
+ company1 | 07-04-2023 | 140 | | |
+ company1 | 07-05-2023 | 150 | | |
+ company1 | 07-06-2023 | 90 | 90 | 120 | 07-07-2023
+ company1 | 07-07-2023 | 110 | | |
+ company1 | 07-08-2023 | 130 | | |
+ company1 | 07-09-2023 | 120 | | |
+ company1 | 07-10-2023 | 130 | | |
+ company2 | 07-01-2023 | 50 | 50 | 1400 | 07-02-2023
+ company2 | 07-02-2023 | 2000 | | |
+ company2 | 07-03-2023 | 1500 | | |
+ company2 | 07-04-2023 | 1400 | | |
+ company2 | 07-05-2023 | 1500 | | |
+ company2 | 07-06-2023 | 60 | 60 | 1200 | 07-07-2023
+ company2 | 07-07-2023 | 1100 | | |
+ company2 | 07-08-2023 | 1300 | | |
+ company2 | 07-09-2023 | 1200 | | |
+ company2 | 07-10-2023 | 1300 | | |
+(20 rows)
+
+SELECT pg_get_viewdef('v_window');
+ pg_get_viewdef
+---------------------------------------------------------------------------------------
+ SELECT company, +
+ tdate, +
+ price, +
+ first_value(price) OVER w AS first_value, +
+ last_value(price) OVER w AS last_value, +
+ nth_value(tdate, 2) OVER w AS nth_second +
+ FROM stock +
+ WINDOW w AS (PARTITION BY company ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING +
+ AFTER MATCH SKIP PAST LAST ROW +
+ INITIAL +
+ PATTERN (start up+ down+) +
+ DEFINE +
+ start AS true, +
+ up AS (price > prev(price)), +
+ down AS (price < prev(price)) );
+(1 row)
+
+--
+-- Error cases
+--
+-- row pattern definition variable name must not appear more than once
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price),
+ UP AS price > PREV(price)
+);
+ERROR: row pattern definition variable name "up" appears more than once in DEFINE clause
+LINE 11: UP AS price > PREV(price),
+ ^
+-- subqueries in DEFINE clause are not supported
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START LOWPRICE)
+ DEFINE
+ START AS TRUE,
+ LOWPRICE AS price < (SELECT 100)
+);
+ERROR: cannot use subquery in DEFINE expression
+LINE 11: LOWPRICE AS price < (SELECT 100)
+ ^
+-- aggregates in DEFINE clause are not supported
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START LOWPRICE)
+ DEFINE
+ START AS TRUE,
+ LOWPRICE AS price < count(*)
+);
+ERROR: aggregate functions are not allowed in DEFINE
+LINE 11: LOWPRICE AS price < count(*)
+ ^
+-- FRAME must start at current row when row patttern recognition is used
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ERROR: FRAME must start at current row when row patttern recognition is used
+-- SEEK is not supported
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ SEEK
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ERROR: SEEK is not supported
+LINE 8: SEEK
+ ^
+HINT: Use INITIAL instead.
+-- PREV's argument must have at least 1 column reference
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(1),
+ DOWN AS price < PREV(1)
+);
+ERROR: row pattern navigation operation's argument must include at least one column reference
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index fbffc67ae60..8051980ab43 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -102,7 +102,7 @@ test: publication subscription
# Another group of parallel tests
# select_views depends on create_view
# ----------
-test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock indirect_toast equivclass
+test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock indirect_toast equivclass rpr
# ----------
# Another group of parallel tests (JSON related)
diff --git a/src/test/regress/sql/rpr.sql b/src/test/regress/sql/rpr.sql
new file mode 100644
index 00000000000..47e67334994
--- /dev/null
+++ b/src/test/regress/sql/rpr.sql
@@ -0,0 +1,486 @@
+--
+-- Test for row pattern definition clause
+--
+
+CREATE TEMP TABLE stock (
+ company TEXT,
+ tdate DATE,
+ price INTEGER
+);
+INSERT INTO stock VALUES ('company1', '2023-07-01', 100);
+INSERT INTO stock VALUES ('company1', '2023-07-02', 200);
+INSERT INTO stock VALUES ('company1', '2023-07-03', 150);
+INSERT INTO stock VALUES ('company1', '2023-07-04', 140);
+INSERT INTO stock VALUES ('company1', '2023-07-05', 150);
+INSERT INTO stock VALUES ('company1', '2023-07-06', 90);
+INSERT INTO stock VALUES ('company1', '2023-07-07', 110);
+INSERT INTO stock VALUES ('company1', '2023-07-08', 130);
+INSERT INTO stock VALUES ('company1', '2023-07-09', 120);
+INSERT INTO stock VALUES ('company1', '2023-07-10', 130);
+INSERT INTO stock VALUES ('company2', '2023-07-01', 50);
+INSERT INTO stock VALUES ('company2', '2023-07-02', 2000);
+INSERT INTO stock VALUES ('company2', '2023-07-03', 1500);
+INSERT INTO stock VALUES ('company2', '2023-07-04', 1400);
+INSERT INTO stock VALUES ('company2', '2023-07-05', 1500);
+INSERT INTO stock VALUES ('company2', '2023-07-06', 60);
+INSERT INTO stock VALUES ('company2', '2023-07-07', 1100);
+INSERT INTO stock VALUES ('company2', '2023-07-08', 1300);
+INSERT INTO stock VALUES ('company2', '2023-07-09', 1200);
+INSERT INTO stock VALUES ('company2', '2023-07-10', 1300);
+
+SELECT * FROM stock;
+
+-- basic test using PREV
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w,
+ nth_value(tdate, 2) OVER w AS nth_second
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+-- basic test using PREV. UP appears twice
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w,
+ nth_value(tdate, 2) OVER w AS nth_second
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+ UP+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+-- basic test using PREV. Use '*'
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w,
+ nth_value(tdate, 2) OVER w AS nth_second
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP* DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+-- basic test with none greedy pattern
+SELECT company, tdate, price, count(*) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A A A)
+ DEFINE
+ A AS price >= 140 AND price <= 150
+);
+
+-- last_value() should remain consistent
+SELECT company, tdate, price, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+-- omit "START" in DEFINE but it is ok because "START AS TRUE" is
+-- implicitly defined. per spec.
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w,
+ nth_value(tdate, 2) OVER w AS nth_second
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+-- the first row start with less than or equal to 100
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (LOWPRICE UP+ DOWN+)
+ DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+-- second row raises 120%
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (LOWPRICE UP+ DOWN+)
+ DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price) * 1.2,
+ DOWN AS price < PREV(price)
+);
+
+-- using NEXT
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UPDOWN)
+ DEFINE
+ START AS TRUE,
+ UPDOWN AS price > PREV(price) AND price > NEXT(price)
+);
+
+-- using AFTER MATCH SKIP TO NEXT ROW
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ INITIAL
+ PATTERN (START UPDOWN)
+ DEFINE
+ START AS TRUE,
+ UPDOWN AS price > PREV(price) AND price > NEXT(price)
+);
+
+-- match everything
+
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ INITIAL
+ PATTERN (A+)
+ DEFINE
+ A AS TRUE
+);
+
+-- backtracking with reclassification of rows
+-- using AFTER MATCH SKIP PAST LAST ROW
+SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ INITIAL
+ PATTERN (A+ B+)
+ DEFINE
+ A AS price > 100,
+ B AS price > 100
+);
+
+-- backtracking with reclassification of rows
+-- using AFTER MATCH SKIP TO NEXT ROW
+SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ INITIAL
+ PATTERN (A+ B+)
+ DEFINE
+ A AS price > 100,
+ B AS price > 100
+);
+
+-- ROWS BETWEEN CURRENT ROW AND offset FOLLOWING
+SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w,
+ count(*) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+--
+-- Aggregates
+--
+
+-- using AFTER MATCH SKIP PAST LAST ROW
+SELECT company, tdate, price,
+ first_value(price) OVER w,
+ last_value(price) OVER w,
+ max(price) OVER w,
+ min(price) OVER w,
+ sum(price) OVER w,
+ avg(price) OVER w,
+ count(price) OVER w
+FROM stock
+WINDOW w AS (
+PARTITION BY company
+ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+AFTER MATCH SKIP PAST LAST ROW
+INITIAL
+PATTERN (START UP+ DOWN+)
+DEFINE
+START AS TRUE,
+UP AS price > PREV(price),
+DOWN AS price < PREV(price)
+);
+
+-- using AFTER MATCH SKIP TO NEXT ROW
+SELECT company, tdate, price,
+ first_value(price) OVER w,
+ last_value(price) OVER w,
+ max(price) OVER w,
+ min(price) OVER w,
+ sum(price) OVER w,
+ avg(price) OVER w,
+ count(price) OVER w
+FROM stock
+WINDOW w AS (
+PARTITION BY company
+ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+AFTER MATCH SKIP TO NEXT ROW
+INITIAL
+PATTERN (START UP+ DOWN+)
+DEFINE
+START AS TRUE,
+UP AS price > PREV(price),
+DOWN AS price < PREV(price)
+);
+
+-- JOIN case
+CREATE TEMP TABLE t1 (i int, v1 int);
+CREATE TEMP TABLE t2 (j int, v2 int);
+INSERT INTO t1 VALUES(1,10);
+INSERT INTO t1 VALUES(1,11);
+INSERT INTO t1 VALUES(1,12);
+INSERT INTO t2 VALUES(2,10);
+INSERT INTO t2 VALUES(2,11);
+INSERT INTO t2 VALUES(2,12);
+
+SELECT * FROM t1, t2 WHERE t1.v1 <= 11 AND t2.v2 <= 11;
+
+SELECT *, count(*) OVER w FROM t1, t2
+WINDOW w AS (
+ PARTITION BY t1.i
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE
+ A AS v1 <= 11 AND v2 <= 11
+);
+
+-- WITH case
+WITH wstock AS (
+ SELECT * FROM stock WHERE tdate < '2023-07-08'
+)
+SELECT tdate, price,
+first_value(tdate) OVER w,
+count(*) OVER w
+ FROM wstock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+-- PREV has multiple column reference
+CREATE TEMP TABLE rpr1 (id INTEGER, i SERIAL, j INTEGER);
+INSERT INTO rpr1(id, j) SELECT 1, g*2 FROM generate_series(1, 10) AS g;
+SELECT id, i, j, count(*) OVER w
+ FROM rpr1
+ WINDOW w AS (
+ PARTITION BY id
+ ORDER BY i
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ INITIAL
+ PATTERN (START COND+)
+ DEFINE
+ START AS TRUE,
+ COND AS PREV(i + j + 1) < 10
+);
+
+-- Smoke test for larger partitions.
+WITH s AS (
+ SELECT v, count(*) OVER w AS c
+ FROM (SELECT generate_series(1, 5000) v)
+ WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ INITIAL
+ PATTERN ( r+ )
+ DEFINE r AS TRUE
+ )
+)
+-- Should be exactly one long match across all rows.
+SELECT * FROM s WHERE c > 0;
+
+WITH s AS (
+ SELECT v, count(*) OVER w AS c
+ FROM (SELECT generate_series(1, 5000) v)
+ WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ INITIAL
+ PATTERN ( r )
+ DEFINE r AS TRUE
+ )
+)
+-- Every row should be its own match.
+SELECT count(*) FROM s WHERE c > 0;
+
+-- View and pg_get_viewdef tests.
+CREATE TEMP VIEW v_window AS
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w,
+ nth_value(tdate, 2) OVER w AS nth_second
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+SELECT * FROM v_window;
+SELECT pg_get_viewdef('v_window');
+
+--
+-- Error cases
+--
+
+-- row pattern definition variable name must not appear more than once
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price),
+ UP AS price > PREV(price)
+);
+
+-- subqueries in DEFINE clause are not supported
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START LOWPRICE)
+ DEFINE
+ START AS TRUE,
+ LOWPRICE AS price < (SELECT 100)
+);
+
+-- aggregates in DEFINE clause are not supported
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START LOWPRICE)
+ DEFINE
+ START AS TRUE,
+ LOWPRICE AS price < count(*)
+);
+
+-- FRAME must start at current row when row patttern recognition is used
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+-- SEEK is not supported
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ SEEK
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+-- PREV's argument must have at least 1 column reference
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(1),
+ DOWN AS price < PREV(1)
+);
--
2.43.0
[application/octet-stream] v33-0008-Row-pattern-recognition-patch-typedefs.list.patch (1.2K, ../[email protected]/9-v33-0008-Row-pattern-recognition-patch-typedefs.list.patch)
download | inline diff:
From 02b6545662e63550238e2af2aa44af7f55936621 Mon Sep 17 00:00:00 2001
From: Tatsuo Ishii <[email protected]>
Date: Wed, 24 Sep 2025 19:18:37 +0900
Subject: [PATCH v33 8/8] Row pattern recognition patch (typedefs.list).
---
src/tools/pgindent/typedefs.list | 6 ++++++
1 file changed, 6 insertions(+)
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 3c80d49b67e..50c9a1b513e 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1744,6 +1744,7 @@ NamedLWLockTrancheRequest
NamedTuplestoreScan
NamedTuplestoreScanState
NamespaceInfo
+NavigationInfo
NestLoop
NestLoopParam
NestLoopState
@@ -2407,6 +2408,8 @@ RI_CompareKey
RI_ConstraintInfo
RI_QueryHashEntry
RI_QueryKey
+RPCommonSyntax
+RPSkipTo
RTEKind
RTEPermissionInfo
RWConflict
@@ -2779,6 +2782,7 @@ SimpleStringList
SimpleStringListCell
SingleBoundSortItem
Size
+SkipContext
SkipPages
SkipSupport
SkipSupportIncDec
@@ -2873,6 +2877,7 @@ StreamStopReason
String
StringInfo
StringInfoData
+StringSet
StripnullState
SubLink
SubLinkType
@@ -3207,6 +3212,7 @@ VarString
VarStringSortSupport
Variable
VariableAssignHook
+VariablePos
VariableSetKind
VariableSetStmt
VariableShowStmt
--
2.43.0
view thread (39+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Row pattern recognition
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox