Re: Row pattern recognition

Hi.

CREATE TABLE stock (company TEXT, tdate DATE, price INTEGER);
CREATE TEMP TABLE stock (company TEXT, tdate DATE, price INTEGER);
SELECT count(*) over w FROM stock WINDOW w AS ( ROWS BETWEEN CURRENT
ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS
pg_temp.stock.price > 0 );
SELECT count(*) over w FROM stock WINDOW w AS ( ROWS BETWEEN CURRENT
ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS public.stock.price
> 0 );
SELECT count(*) over w FROM stock WINDOW w AS ( ROWS BETWEEN CURRENT
ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS stock.price > 0 );

The error messages for the above 3 SELECT queries are different.
(pg_temp.stock.price, public.stock.price, stock.price) mean the same
thing: column reference,
Should we try to make the error messages consistent?

ERROR:  range variable qualified expression "rpr_composite.items" is
not allowed in DEFINE clause

"Range variable qualified expression" is non-standard that may confuse users.
To improve clarity and consistency, let's align this with the
established error pattern:

ERROR: invalid reference to FROM-clause entry for table "the_table"



What do you all think about renaming validateRPRPatternVarCount to
preprocessRPRPattern?
--------------------------------------------------------------------------------------------------
v48 [1] has a conflict with master, so the attached patches are based
on https://github.com/assam258-5892/postgres/commits/RPR

The attached v48-0001 mainly replaces PG_INT32_MAX with
RPR_QUANTITY_INF, but it also includes other changes.
See the commit message for detail.

The attached v48-0002 patch is more about miscellaneous refactoring.
The commit message is included below, enclosed by "-----":
--------------------------------------------------------------------------------------------------
Refactor buildRPRPattern to accept a WindowClause pointer directly. This
eliminates the need to pass internal fields like rpPattern, rpSkipTo, and
frameOptions as separate arguments.

collectDefineVariables is not needed, it's simple, and can integrated its logic
directly into buildRPRPattern easily.  Also removed tryUnwrapSingleChild since
its was simple enough to inline inside optimizeSeqPattern and
optimizeAltPattern.

Slightly adjust variable limits error message.

Restructure multiple INSERT INTO ... VALUES statements into multi-value insert
blocks (e.g., INSERT INTO ... VALUES (), ()). This is for brevity and is a good
practice even though performance gains is minor.

Long PATTERN strings (more than 2000 width!) in the regression tests were broken
into formatted multiline queries for readability.
--------------------------------------------------------------------------------------------------
[1] https://postgr.es/m/20260613.212530.63949290085162247.ishii%40postgresql.org



--
jian
https://www.enterprisedb.com/