Skip site navigation (1) Skip section navigation (2)

Re: view data types

From: Keith Worthington <KeithW(at)NarrowPathInc(dot)com>
To: Michael Fuhr <mike(at)fuhr(dot)org>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: view data types
Date: 2005-07-14 04:34:07
Message-ID: 42D5EB3F.5080602@NarrowPathInc.com (view raw or flat)
Thread:
Lists: pgsql-novice
Michael Fuhr wrote:
> On Wed, Jul 13, 2005 at 10:50:52PM -0400, Keith Worthington wrote:
> 
>>Is there a simple way to determine the data type(s) of columns in a view?
> 
> 
> You could query information_schema.columns or pg_catalog.pg_attribute:
> 
> http://www.postgresql.org/docs/8.0/static/catalog-pg-attribute.html
> http://www.postgresql.org/docs/8.0/static/infoschema-columns.html
> 
> Or you could simply use "\d my_view" in psql, or the equivalent command
> in whatever client you're using.
> 
> 
>>IOW what I would really like to be able to do is
>>
>>SELECT data_type
>>  FROM magic_table
>> WHERE view_name = 'my_view'
>>   AND column_name = 'my_column';
> 
> 
> SELECT data_type
> FROM information_schema.columns
> WHERE table_name = 'my_view'
>   AND column_name = 'my_column';
> 
> or
> 
> SELECT atttypid::regtype
> FROM pg_attribute
> WHERE attrelid = 'my_view'::regclass
>   AND attname = 'my_column';
> 
> For an explanation of regtype and regclass, see "Object Identifier
> Types" in the "Data Types" chapter:
> 
> http://www.postgresql.org/docs/8.0/static/datatype-oid.html

Michael,

Thanks for the post.

While waiting for an answer from the list I puzzled out the following.

SELECT column_type.typname AS data_type
   FROM pg_type AS view_type
   LEFT JOIN pg_attribute
     ON ( view_type.typrelid = pg_attribute.attrelid )
   LEFT JOIN pg_type AS column_type
     ON ( pg_attribute.atttypid = column_type.oid)
  WHERE view_type.typname = 'my_view
    AND pg_attribute.attname = 'my_column';

Your suggestions are much simpler.  The first one worked well while the 
second one errorred out with the message
ERROR:  relation "view_inventory_item" does not exist

A quick tweak adding the schema name like so

SELECT atttypid::regtype
   FROM pg_attribute
  WHERE attrelid = 'my_schema.my_view'::regclass
    AND attname = 'my_column';

and it too worked like a champ.

Now I need to think about whether or not I want to require or reject 
schema qualified view names.

Are there any disadvantages to accessing the pg tables as I have done?

-- 
Kind Regards,
Keith

In response to

pgsql-novice by date

Next:From: Don ParrisDate: 2005-07-14 05:55:48
Subject: Using Batch Files to Create DB Structure
Previous:From: Michael FuhrDate: 2005-07-14 04:05:58
Subject: Re: view data types

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group