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  (Richard Broersma Jr <rabroersma@yahoo.com>)
Список 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 по дате отправления:

Предыдущее
От: PostgreSQL Admin
Дата:
Сообщение: Re: Constraints for grouping
Следующее
От: chester c young
Дата:
Сообщение: Re: Execute SQL statements with 'context'/predefined variables