Hello,
I have a scenario like this:
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;
It's all ok except by the fact that when I retrieve data from view1 the
column type1 become bpchar instead of character(3)
There are some manner to make the view return type1 as character(3) when
I do a command like "select * from view1" ?
It's strangeous because type2 return as integer.
I try to do a
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::character(3) AS type1,
table3.type2
FROM table1
JOIN table3 ON table3.fk_table1 = table1.id;
But i got an error:
ERROR: cannot change data type of view column "type1"
I try this too but got the same error:
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,
''::character(3) AS type1,
table3.type2
FROM table1
JOIN table3 ON table3.fk_table1 = table1.id;
I'm doing a cast on a query on view1 like select type1::character(3)
from view1 , but if exists a manner of do this without this cast it will
much appreciated.
TIA
--
Luiz K. Matsumura
Plan IT Tecnologia Informática Ltda.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
