Cast on character columns in views
От | Luiz K. Matsumura |
---|---|
Тема | Cast on character columns in views |
Дата | |
Msg-id | 46DCD4AC.1060004@planit.com.br обсуждение исходный текст |
Ответы |
Re: Cast on character columns in views
|
Список | pgsql-sql |
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.
В списке pgsql-sql по дате отправления: