Thread: view data types

Hi All,

Is there a simple way to determine the data type(s) of columns in a view?

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';

data_type
---------
      int4

I have been snooping around in the pg tables but I haven't found the
correct one yet.

--
Kind Regards,
Keith

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 Fuhr
http://www.fuhr.org/~mfuhr/

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