Re: view data types - Mailing list pgsql-novice

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/