Message-ID: <46DCD4AC.1060004@planit.com.br>
Date: Tue, 04 Sep 2007 00:44:44 -0300
From: "Luiz K. Matsumura" <luiz@planit.com.br>
Organization: Plan IT Tecnologia =?ISO-8859-1?Q?Inform=E1tica_Ltda=2E?=
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Subject: Cast on character columns in views

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.