From: Luiz K. Matsumura <luiz@planit.com.br>
To: pgsql-sql@postgresql.org
Subject: Cast on character columns in views
Date: Tue, 04 Sep 2007 00:44:44 -0300
Message-ID: <46DCD4AC.1060004@planit.com.br> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: luiz@planit.com.br
Subject: Re: Cast on character columns in views
In-Reply-To: <46DCD4AC.1060004@planit.com.br>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox