Re: SQL equivalent to \dT

Hello

2008/9/2 Bram Kuijper <a(dot)l(dot)w(dot)kuijper(at)rug(dot)nl>:
> Hi all,
>
> if I want to get a list of types (ie., data types or enums), then I can use
> the '\dT' command from within the postgreSQL client.
>

run psql with -E parameter. You will see all SQL statements used for
metacommands.

[pavel(at)localhost ~]$ psql -E postgres
psql (8.4devel)
Type "help" for help.

postgres=# \dT
********* QUERY **********
SELECT n.nspname as "Schema",
pg_catalog.format_type(t.oid, NULL) AS "Name",
pg_catalog.obj_description(t.oid, 'pg_type') as "Description"
FROM pg_catalog.pg_type t
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
WHERE (t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM
pg_catalog.pg_class c WHERE c.oid = t.typrelid))
AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid =
t.typelem AND el.typarray = t.oid)
AND pg_catalog.pg_type_is_visible(t.oid)
ORDER BY 1, 2;
**************************

List of data types
Schema | Name |
Description
------------+-----------------------------+-------------------------------------------------------------------
pg_catalog | abstime | absolute, limited-range
date and time (Unix system time)
pg_catalog | aclitem | access control list
pg_catalog | "any" |
pg_catalog | anyarray |
pg_catalog | anyelement |

regards
Pavel Stehule

> However, I cannot seem to figure out what the SQL alternative is to the \dT
> command, so that I might get a list of types scriptable by SQL.
>
> For example, if I create an ENUM myself:
> CREATE TYPE bird AS ENUM('duck','goose');
>
> a quick look through the various parts of the information schema did not
> reveal in which place this enum is stored. Is the information schema the
> correct place to look for this? Which SQL statement do I need to get a list
> of user-defined types?
>
> thanks in advance,
>
> Bram Kuijper
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>