Horton

Re: Row pattern recognition

Tatsuo Ishii <ishii@postgresql.org>

View thread
From: Tatsuo Ishii <ishii@postgresql.org>
To: assam258@gmail.com
Cc: zsolt.parragi@percona.com, sjjang112233@gmail.com, vik@postgresfriends.org, er@xs4all.nl, jacob.champion@enterprisedb.com, david.g.johnston@gmail.com, peter@eisentraut.org, li.evan.chao@gmail.com, jian.universality@gmail.com, pgsql-hackers@postgresql.org
Date: 2026-05-10T02:54:37Z
Lists: pgsql-hackers 
Hi Henson,

> Still deferred:
>   - B7 Recursive CTE XXX: pending community input on the
>     ISO/IEC 19075-5 6.17.5 / 4.18.5 interpretation.

ISO/IEC 19075-5 6.17.5 stats that "Row pattern matching is prohibited
in recursive queries. For example, the following is a syntax error:

CREATE RECURSIVE VIEW Problem... SELECT Kolo, Xoro FROM Ticker... WINDOW W AS... MEASURES..."

ISO/IEC 19075-5 4.18.5 stats that "Row pattern matching is prohibited
in recursive queries. For example, the following is a syntax error:

CREATE RECURSIVE VIEW Problem... SELECT Kolo, Xoro FROM t... MATCH_REZOGNIZE..."

From these it is apparent that CREATE RECURSIVE VIEW cannot be used
with RPR in both R010 and R020.

Question is, what about CTE queries?

I looked into ISO/IEC 9075-2:2016 (I don't have access to 2023) and
found this in "7.17 <query expression>".

in "Syntax Rule"

3) If <with clause> WC is specified, then:

a) Let n be the number of <with list element>s.
:
:
3) If <with clause> WC is specified, then:
:
:
e) If WC immediately contains RECURSIVE, then WC, its <with list>, and
   its <with list element>s are said to be potentially
   recursive. Otherwise, they are said to be non-recursive.

f) A potentially recursive <with list element> shall not contain a
   <row pattern measures> or <row pattern common syntax>.

So I think at least SQL:2016 explicitly prohibits using RPR within
recursive CTE. I would appreciate if anybody confirms this in
SQL:2023.

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