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

On Fri, Dec 21, 2012 at 9:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.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