Re: Row pattern recognition - Mailing list pgsql-hackers

I gave a talk on RPR in PGConf.dev 2024.
https://www.pgevents.ca/events/pgconfdev2024/schedule/session/114-implementing-row-pattern-recognition/
(Slides are available from the link).

Vik Faring and Jacob Champion were one of the audiences and we had a
small discussion after the talk. We continued the discussion off list
on how to move forward the RPR implementation project. One of the
ideas is, to summarize what are in the patch and what are not from the
SQL standard specification's point of view. This should help us to
reach the consensus regarding "minimum viable" feature set if we want
to bring the patch in upcoming PostgreSQL v18.

Here is the first cut of the document. Comments/feedback are welcome.

-------------------------------------------------------------------------
This memo describes the current status of implementation of SQL/RPR
(Row Pattern Recognition), as of June 13, 2024 (the latest patch is v20).

- RPR in FROM clause and WINDOW clause

The SQL standard defines two features regarding SQL/RPR - R010 (RPR in
FROM clause) and R020 (RPR in WINDOW clause). Only R020 is
implemented. From now on, we discuss on R020.

- Overview of R020 syntax

WINDOW window_name AS (
[ PARTITION BY ... ]
[ ORDER BY... ]
[ MEASURES ... ]
ROWS BETWEEN CURRENT ROW AND ...
[ AFTER MATCH SKIP ... ]
[ INITIAL|SEEK ]
PATTERN (...)
[ SUBSET ... ]
DEFINE ...
)

-- PARTITION BY and ORDER BY are not specific to RPR and has been
  already there in current PostgreSQL.

-- What are (partially) implemented:

AFTER MATCH SKIP
INITIAL|SEEK
PATTERN
DEFINE

-- What are not implemented at all:
MEASURES
SUBSET

Followings are detailed status of the each clause.

- AFTER MATCH SKIP

-- Implemented:
AFTER MATCH SKIP TO NEXT ROW
AFTER MATCH SKIP PAST LAST ROW

-- Not implemented:
AFTER MATCH SKIP TO FIRST|LAST pattern_variable

- INITIAL|SEEK

--Implemented:
INITIAL

-- Not implemented:
SEEK

- DEFINE

-- Partially implemented row pattern navigation operations are PREV and
   NEXT. FIRST and LAST are not implemented.

-- The standard says PREV and NEXT accepts optional argument "offset"
   but it's not implemented.

-- The standard says the row pattern navigation operations can be
   nested but it's not implemented.

-- CLLASSIFIER, use of aggregate functions and subqueries in DEFINE
   clause are not implemented.

- PATTERN

-- Followings are implemented:
+: 1 or more rows
*: 0 or more rows

-- Followings are not implemented:
?: 0 or 1 row
A | B: OR condition
(A B): grouping
{n}: n rows
{n,}: n or more rows
{n,m}: greater or equal to n rows and less than or equal to m rows
{,m}: more than 0 and less than or equal to m rows
-------------------------------------------------------------------------

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp