Re: view data types

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