From: | "Luiz K(dot) Matsumura" <luiz(at)planit(dot)com(dot)br> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Cast on character columns in views |
Date: | 2007-09-04 03:44:44 |
Message-ID: | 46DCD4AC.1060004@planit.com.br |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | chester c young | 2007-09-04 04:08:37 | Re: Execute SQL statements with 'context'/predefined variables |
Previous Message | PostgreSQL Admin | 2007-09-04 00:21:13 | Re: Constraints for grouping |