Thread

Re: Row pattern recognition

Tatsuo Ishii <ishii@postgresql.org> — 2025-12-01T06:57:02Z

Hi Chao,

Sorry, I missed this email.

>> 9 - 0002 - parse_clause.c
> 
> I am continuing to review 0003
> 
> 10 - 0003
> ```
> +	Assert(list_length(patternVariables) == list_length(defineClause));
> ```
> 
> Is this assert true? From what I learned, pattern length doesn’t have to equal to define length. For example, I got an example from [1]:

You are right. If PATTERN clause uses the same pattern variable more
than once (which is perfectly valid), the assertion fails. I will
remove the assersion and fix the subsequent forboth loop.

> ```
> Example 4
> -- This example has three different patterns.
> -- Comment two of them, to get error-free query.
> SELECT company, price_date, price
>   FROM stock_price
>     MATCH_RECOGNIZE (
>        partition by company
>        order by price_date
>        all rows per match
>        pattern ( limit_50  up   up* down  down*  )
>        define
>            limit_50 as price <= 50.00,
>            up   as price > prev(price),
>            down as price < prev(price)
>     )
>    WHERE company = 'B'
>    ORDER BY price_date;
> ```
> 
> In this example, pattern has 5 elements and define has only 3 elements.

Yes.

> 11 - 0004 - plannodes.h
> ```
> +	/* Row Pattern Recognition AFTER MACH SKIP clause */
> +	RPSkipTo	rpSkipTo;		/* Row Pattern Skip To type */
> ```
> 
> Typo: MACH -> MATCH

Will fix/

> I’d stop here, and continue 0005 tomorrow.

Thanks!

> [1] https://link.springer.com/article/10.1007/s13222-022-00404-3
> 
> Best regards,
> --
> Chao Li (Evan)
> HighGo Software Co., Ltd.
> https://www.highgo.com/
> 
> 
> 
>