A problem with dump/restore of views containing whole row references - Mailing list pgsql-hackers

Hi,

This is the version I used to run the following commands

select version();
                                                    version                                                    
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.2devel on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.4.4-14ubuntu5) 4.4.5, 64-bit
(1 row)

Run these commands

  CREATE TABLE price (id INT PRIMARY KEY, active BOOLEAN NOT NULL, price NUMERIC);
  insert into price values (1,false,42), (10,false,100), (11,true,17.99);
  create view v2 as select price.*::price from price;
  select * from v2;
      price    
  --------------
   (1,f,42)
   (10,f,100)
   (11,t,17.99)
  (3 rows)

  \d+ v2;
                    View "public.v2"
   Column | Type  | Modifiers | Storage  | Description
  --------+-------+-----------+----------+-------------
   price  | price |           | extended |
  View definition:
   SELECT price AS price
     FROM price;

Note the output from the view, also note the "Type" in view defination.

Now take dump of this database.

./pg_dump --file=/home/user_name/d.sql --format=p --inserts -p 4444 test

The dump file is attached with the mail. (d.sql)

Now lets restore this dump.

./createdb test2 -p 4444
./psql -p 4444 -f /home/user_name/d.sql test2
./psql test2 -p 4444
psql (9.2devel)
Type "help" for help.

test2=# select * from v2;
 price
-------
    42
   100
 17.99
(3 rows)

test2=# \d+ v2
                   View "public.v2"
 Column |  Type   | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
 price  | numeric |           | main    |
View definition:
 SELECT price.price
   FROM price;

In the database test2 the view was not restored correctly.
The output of the view as well as the Type in its defination is wrong.

The cause of the problem is as follows

The notation "relation.*" represents a whole-row reference.
While parsing a whole-row reference is transformed into a Var with varno set to the correct range table entry,
and varattno == 0 to signal that it references the whole tuple. (For reference see comments of function makeWholeRowVar)
While deparsing we need to take care of this case.
The attached patch provides deparsing of a whole-row reference.
A whole row reference will be deparsed either into alias.*::relation or relation.*::relation depending on alias

--
Abbas
EnterpriseDB Corporation
The Enterprise PostgreSQL Company