Thread

Re: Row pattern recognition

Tatsuo Ishii <ishii@postgresql.org> — 2026-05-05T00:01:24Z

> Attached is the v47 patches for Row pattern recognition (SQL/RPR).

v47 starts to check whether range variable qualified expressions are
used in DEFINE clause. If used, raise an error. This is good because
we don't support the syntax yet (pattern variable range var), or it's
prohibited (from clause range var). However, the error message may not
be appropreate for the case when complex data type is involved.

CREATE TEMP TABLE item (name TEXT, amount INT);
CREATE TABLE
CREATE TEMP TABLE sales(items item);
CREATE TABLE
SELECT (items).name, (items).amount, count(*) OVER w
FROM sales WINDOW w AS (
    ORDER BY (items).name
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+)
    DEFINE A AS (A.items).amount > 10
);
ERROR:  pattern variable qualified column reference "a.items" is not supported in DEFINE clause
LINE 7:     DEFINE A AS (A.items).amount > 10
                         ^
If I change the DEFINE clause to:

    DEFINE A AS (sales.items).amount > 10

I get:

ERROR:  range variable qualified column reference "sales.items" is not allowed in DEFINE clause
LINE 8:     DEFINE A AS (sales.items).amount > 10
                         ^

In both cases, "a.items" or "sales.items" in the error messages are
not column names, therefore the wording "column reference" in the
error messages are not appropriate.

The checks are in transformColumnRef().  Its argument "ColumnRef
*cref->fields" is a list of column ref fields in DEFINE expression in
the raw parse tree.  Usually it's something like range_var.col_name or
just col_name etc. So it works. However when complex data types are
involved, cref->fields is something like sales.items. No col_name
included. In this case, ColumnRef is under Indirection node, and the
col_name is stored in Indirection->indirection, not in ColumnRef.
Therefore there's no way for transformColumnRef() to know the col_name.

In order to fix the issue, I think we need to add code to understand
range var qualification form "A.item" or "sales.items" in complex data
types case. But is it worth the trouble? The reword is just nicer
error messages. If we support MEASURES, the code is useful. But so far
we have decided to not support it in the first cut of RPR.

Maybe we should just change the error messages something like below
for now?

ERROR:  pattern variable qualified expression "a.items" is not supported in DEFINE clause
ERROR:  range variable qualified expression "sales.items" is not allowed in DEFINE clause

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