Re: Row pattern recognition

I have created v50 RPR patches from patches (nocfbot-000*.txt and
nocfbot-100*.txt) attached to the following email:
https://www.postgresql.org/message-id/CAAAe_zDF-jqo=m+pcuPB1h_sG1t+nY08acjBEcfD=1KoQN4JOQ@mail.gmail.com

The series of patches are to implement the row pattern recognition
(SQL/RPR) feature. Currently the implementation is a subset of SQL/RPR
(ISO/IEC 19075-2:2016). Namely, implementation of some features of
R020 (WINDOW clause). R010 (MATCH_RECOGNIZE) is out of the scope of
the patches.

Currently following features are implemented in the patches.

- PATTERN
- PATTERN regular expressions (+, *, ?)
alternation (|), grouping () , {n}, {n,}, {n,m}, {,m}
reluctant quantifiers (*? etc.),
Empty pattern ("PATTERN ()") are not permitted by the standard
Anchors (^, $) are not permitted in R020 by the standard
- DEFINE
- INITIAL
- AFTER MATCH SKIP TO PAST LAST ROW
- AFTER MATCH SKIP TO NEXT ROW
- Row pattern navigation (FIRST, LAST, PREV, NEXT and their compound forms)

Currently following features are not implemented in the patches.

- MEASURES
- Pattern variable name qualified column reference (e.g. A.price)
- SUBSET
- SEEK
- AFTER MATCH SKIP TO
- AFTER MATCH SKIP TO FIRST
- AFTER MATCH SKIP TO LAST
- PATTERN regular expression {- and -}
- PERMUTE
- CLASSIFIER

Author: Tatsuo Ishii <ishii(at)postgresql(dot)org>
Author: Henson Choi <assam258(at)gmail(dot)com>
Reviewed-by: Vik Fearing <vik(at)postgresfriends(dot)org>
Reviewed-by: Jacob Champion <jchampion(at)timescale(dot)com>
Reviewed-by: Peter Eisentraut <peter(at)eisentraut(dot)org>
Reviewed-by: NINGWEI CHEN <chen(at)sraoss(dot)co(dot)jp>
Reviewed-by: "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
Reviewed-by: Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com>
Reviewed-by: SungJun Jang <sjjang112233(at)gmail(dot)com>
Reviewed-by: Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com>
Reviewed-by: jian he <jian(dot)universality(at)gmail(dot)com>

Thanks to Jian He for an extensive review that helped surface several
defects and substantially improved the readability and maintainability
of the code; the review also prompted the redesign of the
window-navigation functions.

Discussion: https://postgr.es/m/20230625.210509.1276733411677577841.t-ishii%40sranhm.sra.co.jp

Major changes since v49 include:

- Placefolder functions for row pattern navigation operations (PREV,
NEXT, FIRST, and LAST) are no longer used. Parse/analysis turned
them into RPRNavExpr nodes. Now RPR patches do not include any
system catalog changes. Also no changes are made to windowfuncs.c as
it does not need to include placeholder functions for RPR's PREV
navigation operators.

Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp