Re: Row pattern recognition

> I played with following SQL to check if the technique using subquery
> to resolve an ambiguity of column names in DEFINE clause explained in
> ISO/IEC 19075-5 section 6.5 "Row pattern variables and other range
> variables".
>
> First create an ambiguous column "val" in DEFINE clause.
>
> CREATE TABLE t1 (id int, val int);
> CREATE TABLE
> CREATE TABLE t2 (id int, val int);
> CREATE TABLE
> INSERT INTO t1 VALUES(1,1),(2,2);
> INSERT 0 2
> INSERT INTO t2 VALUES(1,-1),(2,-2);
> INSERT 0 2
> SELECT FROM t1, t2 WHERE t1.id = t2.id
> WINDOW w AS (
> ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
> PATTERN(A)
> DEFINE A AS val > 0
> );
> psql:ambiguous.sql:13: ERROR: column reference "val" is ambiguous
> LINE 5: DEFINE A AS val > 0
> ^
> Next, use the subquery workaround:
>
> SELECT tt.id1, tt.val1, ttt.id, ttt.val, count(*) OVER w
> FROM (SELECT id AS id1, val AS val1 FROM t1) AS tt, t2 AS ttt
> WHERE tt.id1 = ttt.id
> WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
> PATTERN(A)
> DEFINE A AS val < 0
> );
> id1 | val1 | id | val | count
> -----+------+----+-----+-------
> 1 | 1 | 1 | -1 | 1
> 2 | 2 | 2 | -2 | 1
> (2 rows)
>
> Seems work. Now, create a view from the query.
>
> CREATE VIEW v1 AS
> SELECT tt.id1, tt.val1, ttt.id, ttt.val, count(*) OVER w
> FROM (SELECT id AS id1, val AS val1 FROM t1) AS tt, t2 AS ttt
> WHERE tt.id1 = ttt.id
> WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
> PATTERN(A)
> DEFINE A AS val < 0
> );
> CREATE VIEW
> SELECT * FROM v1;
> id1 | val1 | id | val | count
> -----+------+----+-----+-------
> 1 | 1 | 1 | -1 | 1
> 2 | 2 | 2 | -2 | 1
> (2 rows)
>
> Again, it works. Let's check the view definition.
>
> SELECT pg_get_viewdef('v1'::regclass, true);
> pg_get_viewdef
> ------------------------------------------------------------------
> SELECT tt.id1, +
> tt.val1, +
> ttt.id, +
> ttt.val, +
> count(*) OVER w AS count +
> FROM ( SELECT t1.id AS id1, +
> t1.val AS val1 +
> FROM t1) tt, +
> t2 ttt +
> WHERE tt.id1 = ttt.id +
> WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING +
> AFTER MATCH SKIP PAST LAST ROW +
> INITIAL +
> PATTERN (a) +
> DEFINE +
> a AS ttt.val < 0 );
> (1 row)
>
> Not good. The DEFINE clause uses a range variable declared in the FROM
> clause (ttt.val), which is not valid. Of course if we dump/restore
> this, restore will fail.
>
> So, even if we do not use ALTER TABLE ADD COLULN/RENAME COLUMN, we
> have a problem with views using RPR. I have not checked how hard to
> fix this yet. If it's hard, probably we should add this as a
> limitation of RPR to the document.

Attached is a patch trying to fix the issue (againt today's master
rebased v50). Now it produces following result, which looks OK to me.

SELECT pg_get_viewdef('v1'::regclass, true);
pg_get_viewdef
------------------------------------------------------------------
SELECT tt.id1, +
tt.val1, +
ttt.id, +
ttt.val, +
count(*) OVER w AS count +
FROM ( SELECT t1.id AS id1, +
t1.val AS val1 +
FROM t1) tt, +
t2 ttt +
WHERE tt.id1 = ttt.id +
WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING +
AFTER MATCH SKIP PAST LAST ROW +
INITIAL +
PATTERN (a) +
DEFINE +
a AS val < 0 );
(1 row)

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