Re: Row pattern recognition

Hi Henson,

> Hi Tatsuo,
>
> Here are ten incremental patches on top of v43.

Thanks.

BTW, I noticed that RPR accepts table name qualified variables in
DEFINE clause.

SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w,
nth_value(tdate, 2) OVER w AS nth_second
FROM stock
WINDOW w AS (
PARTITION BY company
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
INITIAL
PATTERN (START UP+ DOWN+)
DEFINE
START AS TRUE,
UP AS stock.price > PREV(price), <-- table name "stock" is uded
DOWN AS price < PREV(price)
);

The standard does not allow to use range variables, declared in the
FROM clause, in the DEFINE clause (19075-5 6.5). Attached patch raises
an error in this case.

psql:a.sql:13: ERROR: range var qualified name "stock.price" is not allowed in DEFINE clause
LINE 11: UP AS stock.price > PREV(price),
^

Also, currently we do not support pattern variable range vars in the
DEFINE caluse (e.g. UP.price). If used, we see a confusing error
message:

ERROR: missing FROM-clause entry for table "UP"
LINE 13: UP AS UP.price > PREV(price),
^

The attached patch errors out differently. I believe this is an
enhancement.

psql:a.sql:13: ERROR: range var qualified name "up.price" is not allowed in DEFINE clause
LINE 11: UP AS UP.price > PREV(price),
^

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