Message-ID: <46DD0A5E.9010400@planit.com.br>
Date: Tue, 04 Sep 2007 04:33:50 -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
References: <144384.86744.qm@web31802.mail.mud.yahoo.com>
	<46DCFD6D.20803@planit.com.br>
In-Reply-To: <46DCFD6D.20803@planit.com.br>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Subject: Re: Cast on character columns in views


Luiz K. Matsumura wrote:
>
> Richard Broersma Jr wrote:
>> --- "Luiz K. Matsumura" <luiz@planit.com.br> wrote:
>>  
>>> CREATE VIEW view1( id, col1, type1, type2) AS
>>> SELECT table1.id,
>>>        table1.col1,
>>>        CAST( table2.type1 AS CHARACTER( 3 )),
>>>        NULL
>>> FROM   table1
>>> JOIN   table2 ON table2.fk_table1 = table1.id
>>> UNION ALL
>>> SELECT table1.id,
>>>        table1.col1,
>>>        CAST( NULL AS CHARACTER( 3 )),
>>>        table3.type2
>>> FROM   table1
>>> JOIN   table3 ON table3.fk_table1 = table1.id;
>>>     
>>
>> Would the above changes work?
>>
>> Regards,
>> Richard Broersma Jr.
>>
>>
>>   
> Hi Richard,
> Your changes works ! But now I know what mistake I did:
>
> The error is occurring because I'm doing a CREATE OR REPLACE VIEW 
> command.
> The command with null:character(3) works too.
> The error is because I create a view then try to change the definition 
> with the CREATE OR REPLACE VIEW command
> When I drop the view first , and then create again the view (in a 
> separated transaction), now the command works! (this is a bug?)
> I'm using a postgres 8.2.4 on Linux.
>
> Thanks a lot!
>
Ugh, I'm totally crazy with this views
I'm using pgadmin with postgres, when I  clink on "view the data of 
selected object" button all works fine.
But when I open a query tool window and do:

SELECT * FROM view1;

Now, again type1 column returns as bpchar.
But if I do:

SELECT type1 FROM view1;

Now, type1 column returns as character(3)

If I do

select id, col1, type1,type2  from view1;

Again type1 returns as bpchar. But if I do

select id, col1,type2, type1 from view1;

Now type1 return as character(3).


-- 
Luiz K. Matsumura
Plan IT Tecnologia Informática Ltda.