Cast on character columns in views

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.

Responses

Browse pgsql-sql by date

  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