Re: Making view dump/restore safe at the column-alias level

On Fri, Dec 21, 2012 at 9:46 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> I'm having a hard time following this. Can you provide a concrete example?
>
> regression=# create table t1 (x int, y int);
> CREATE TABLE
> regression=# create table t2 (x int, z int);
> CREATE TABLE
> regression=# create view v1 as select * from t1 join t2 using (x);
> CREATE VIEW
> regression=# \d+ v1
> View "public.v1"
> Column | Type | Modifiers | Storage | Description
> --------+---------+-----------+---------+-------------
> x | integer | | plain |
> y | integer | | plain |
> z | integer | | plain |
> View definition:
> SELECT t1.x, t1.y, t2.z
> FROM t1
> JOIN t2 USING (x);
> regression=# alter table t2 rename column x to q;
> ALTER TABLE
> regression=# \d+ v1
> View "public.v1"
> Column | Type | Modifiers | Storage | Description
> --------+---------+-----------+---------+-------------
> x | integer | | plain |
> y | integer | | plain |
> z | integer | | plain |
> View definition:
> SELECT t1.x, t1.y, t2.z
> FROM t1
> JOIN t2 USING (x);
>
> At this point the dumped view definition is wrong: if you try to execute
> it you get
>
> regression=# SELECT t1.x, t1.y, t2.z
> regression-# FROM t1
> regression-# JOIN t2 USING (x);
> ERROR: column "x" specified in USING clause does not exist in right table
>
> I'm suggesting that we could fix this by emitting something that forces
> the right alias to be assigned to t2.q:
>
> SELECT t1.x, t1.y, t2.z
> FROM t1
> JOIN t2 AS t2(x,z)
> USING (x);

Sneaky. I didn't know that would even work, but it seems like a
sensible approach.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company