data type change on a view

On Dec 12, 2007 12:11 PM, A. Kretschmer
<andreas.kretschmer@schollglas.com> wrote:

Hello @all,

i have a question (rot really for myself, a member of ther german forum
asks):

i have two tables, contains a varchar(N)-column. Now i create a VIEW
based on this tables. The resulting view contains now a varchar without
length. How can i prevent this? How can i force that the column in the
view contains the *exact* typ?

cast it to varchar(8):

test=# create table h1 (t varchar(8));
CREATE TABLE
test=*# create table h2 (t varchar(8));
CREATE TABLE
test=*# create view h as select t::varchar(8) from h1 union all select
t from h2;
CREATE VIEW
test=*# \d h
View "public.h"
Column | Type | Modifiers
--------+-------------------+-----------
t | character varying(8) |

Note that I don't have to do that in 8.2.5, it's automagic...

Scott Marlowe <scott.marlowe@gmail.com> schrieb:

On Dec 12, 2007 12:11 PM, A. Kretschmer
<andreas.kretschmer@schollglas.com> wrote:

Hello @all,

i have a question (rot really for myself, a member of ther german forum
asks):

i have two tables, contains a varchar(N)-column. Now i create a VIEW
based on this tables. The resulting view contains now a varchar without
length. How can i prevent this? How can i force that the column in the
view contains the *exact* typ?

cast it to varchar(8):

As i said in a private mail to Scott (sorry): the suggested way don't
work, at least with 8.1. Maybe this works better in more recent
versions.

But thx for the quick response.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

Andreas Kretschmer <akretschmer@spamfence.net> writes:

cast it to varchar(8):

As i said in a private mail to Scott (sorry): the suggested way don't
work, at least with 8.1. Maybe this works better in more recent
versions.

Yes, it works a lot better in 8.2:
http://archives.postgresql.org/pgsql-committers/2006-08/msg00222.php

If you really need the right output type in 8.1, you could force the
issue with an extra level of sub-select:

regression=# create table foo (f1 varchar(8));
CREATE TABLE
regression=# create view voo as select f1::varchar(8) from (select * from foo union select * from foo) ss;
CREATE VIEW
regression=# \d voo
View "public.voo"
Column | Type | Modifiers
--------+----------------------+-----------
f1 | character varying(8) |
View definition:
SELECT ss.f1::character varying(8) AS f1
FROM ( SELECT foo.f1
FROM foo
UNION
SELECT foo.f1
FROM foo) ss;

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> schrieb:

Andreas Kretschmer <akretschmer@spamfence.net> writes:

cast it to varchar(8):

As i said in a private mail to Scott (sorry): the suggested way don't
work, at least with 8.1. Maybe this works better in more recent
versions.

Yes, it works a lot better in 8.2:
http://archives.postgresql.org/pgsql-committers/2006-08/msg00222.php

Thx you very much for the answer and the link.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�