BUG #3597: CREATE OR REPLACE VIEW - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      3597
Logged by:          Luiz K. Matsumura
Email address:      luiz@planit.com.br
PostgreSQL version: 8.2.4
Operating system:   Fedora core 3
Description:        CREATE OR REPLACE VIEW
Details:

scenario:

CREATE TABLE table1
(
 id serial NOT NULL,
 col1 character varying(30),
 CONSTRAINT pk_table1 PRIMARY KEY (id)
);

CREATE TABLE table2
(
 fk_table1 integer,
 type1 character(3),
 id serial NOT NULL,
 CONSTRAINT pk_table2 PRIMARY KEY (id)
);

CREATE TABLE table3
(
 id serial NOT NULL,
 type2 integer,
 fk_table1 integer,
 CONSTRAINT pk_table3 PRIMARY KEY (id)
);

CREATE VIEW view1 AS
SELECT table1.id,
      table1.col1,
      table2.type1,
      NULL AS type2
FROM   table1
JOIN   table2 ON table2.fk_table1 = table1.id
UNION ALL
SELECT table1.id,
      table1.col1,
      NULL AS type1,
      table3.type2
FROM   table1
JOIN   table3 ON table3.fk_table1 = table1.id;

When we do a command Create or Replace View that change columns of previous
view we got a error.

Ex.:

CREATE OR REPLACE VIEW view1 AS
SELECT table1.id,
      table1.col1,
      table2.type1,
      NULL AS type2
FROM   table1
JOIN   table2 ON table2.fk_table1 = table1.id
UNION ALL
SELECT table1.id,
      table1.col1,
      NULL::character(3) AS type1,
      table3.type2
FROM   table1
JOIN   table3 ON table3.fk_table1 = table1.id;


ERROR: cannot change data type of view column "type1"