Hey hackers -
I don't know if this is fixed in 6.5.1 or not, but the definition field
in the pg_views system table is broken in 6.5.0, and this breaks view
editing in pgaccess. The problem is that table qualifications are left
off the fieldnames in both the SELECT clause and the WHERE clause. Minimal
example given below:
test=> create table t1 (textid int4, nextid int4, words text);
CREATE
test=> create table t2 (nextid int4, words text);
CREATE
test=> create view v1 as select t1.textid,t1.words,t2.words as words2
from t1,t2 where t1.nextid=t2.nextid;
CREATE
test=> insert into t1 values (2,1,'some other text');
INSERT 384454 1
test=> insert into t2 values (1,'joint text');
INSERT 384455 1
test=> insert into t1 values (1,1,'some text');
INSERT 384456 1
test=> select * from v1;
textid|words |words2
------+---------------+---------- 2|some other text|joint text 1|some text |joint text
(2 rows)
test=> select definition from pg_views where viewname='v1';
definition
-----------------------------------------------------------------------
SELECT "textid", "words", "words" AS "words2" FROM "t1", "t2" WHERE
"nextid" = "nextid"; (1 row)
test=> SELECT "textid", "words", "words" AS "words2" FROM "t1", "t2"
WHERE "nextid" = "nextid";
ERROR: Column 'words' is ambiguous
test=>
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
