Thread: data type change on a view

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?


Example:

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 from h1 union all select t from h2;
CREATE VIEW
test=*# \d h
            View "public.h"
 Column |       Type        | Modifiers
--------+-------------------+-----------
 t      | character varying |


thx, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

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°