I created the following example table to get all ABC matches with SQL’s MATCH_RECOGNIZE:

id | value
----+-------
  1 | A
  2 | B
  3 | A
  4 | B
  5 | A
  6 | C

With this, I want to get all 3 ABC matches:

1. 1A 2B 6C

2. 1A 4B 6C

3. 3A 4B 6C

I tried this query:

select * from abc
            -> match_recognize(
            -> order by id
            -> measures
            -> classifier() as val
            -> all rows per match 
            -> after match skip to next row
            -> pattern(A {-ANYTHING*-} B {-ANYTHING*-} C)
            -> define A as value = 'A', B as value = 'B', C as value = 'C');

The problem is that when I skip a row with after match skip to next row, it misses a match. And if I would use after match skip to first A it would result in an error as this would create an infinite loop.

Is it even possible to find all matches?