> 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
diff --git a/src/backend/utils/adt/ruleutils.c
b/src/backend/utils/adt/ruleutils.c
index 2ee9928de2c..aa5224a27fd 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -8560,8 +8560,13 @@ get_variable(Var *var, int levelsup, bool istoplevel,
deparse_context *context)
attname = get_rte_attribute_name(rte, attnum);
}
- need_prefix = (context->varprefix || attname == NULL ||
- var->varreturningtype !=
VAR_RETURNING_DEFAULT);
+ /*
+ * Vars in DEFINE clause never need table-name prefix.
+ */
+ need_prefix = false;
+ if (!context->inRPRDefine)
+ need_prefix = (context->varprefix || attname == NULL ||
+ var->varreturningtype !=
VAR_RETURNING_DEFAULT);
/*
* If we're considering a plain Var in an ORDER BY (but not GROUP BY)
