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