Re: Listing only the user defined types (with owners) - Mailing list pgsql-general

From Kashif Zeeshan
Subject Re: Listing only the user defined types (with owners)
Date
Msg-id CAAPsdhf42kFkq6ZeuX1q+qW=H7XaxdiBYzWCooMnWspM=4s8_w@mail.gmail.com
Whole thread Raw
In response to Listing only the user defined types (with owners)  (Durumdara <durumdara@gmail.com>)
List pgsql-general
Hi

You can find all user defined types with the following query.

CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');

SELECT typname
FROM pg_catalog.pg_type
  JOIN pg_catalog.pg_namespace
  ON pg_namespace.oid = pg_type.typnamespace
WHERE
typtype = 'e' and nspname NOT IN ('pg_catalog', 'information_schema');
        typname
-----------------------
 bug_status

The values for typtype are as follows
typtype is b for a base type, c for a composite type (e.g., a table's row type), d for a domain, e for an enum type, p for a pseudo-type, or r for a range type. See also typrelid and typbasetype.


Regards
Kashif Zeeshan
Bitnine Global

On Thu, May 2, 2024 at 4:40 PM Durumdara <durumdara@gmail.com> wrote:
Hello!

I have a script which can change the table owners to the database owner.

I select the tables like this:

    FOR r IN SELECT tablename FROM pg_tables WHERE (schemaname = 'public') and (tableowner <> act_dbowner)
    LOOP
...

For types I found pg_type, but this contains all types.

For example I have only one user defined type, like "T_TEST", but this pg_type relation contains the basic data types,  other data types, from any schema.

Do you have a working Query which lists the user defined types with the owners?

Thank you for your help!

Best regards
dd


pgsql-general by date:

Previous
From: Durumdara
Date:
Subject: Listing only the user defined types (with owners)
Next
From: "David G. Johnston"
Date:
Subject: Re: Prevent users from executing pg_dump against tables