Subject: Listing only the user defined types (with owners)

From: Durumdara <[email-masked]>
Date: Thu, 2 May 2024 13:39:36 +0200

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

---

From: Kashif Zeeshan <[email-masked]>
Date: Thu, 2 May 2024 17:06:35 +0500

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

---

From: Thom Brown <[email-masked]>
Date: Thu, 2 May 2024 13:28:56 +0100

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

You can always cheat and copy what psql does when you tell it to list all
user types with extended output (\dt+):

postgres=# SET log_min_duration_statement = 0;
SET
postgres=# SET client_min_messages TO LOG;
LOG:  duration: 0.137 ms  statement: SET client_min_messages TO LOG;
SET
postgres=# \dT+
LOG:  duration: 2.901 ms  statement: SELECT n.nspname as "Schema",
  pg_catalog.format_type(t.oid, NULL) AS "Name",
  t.typname AS "Internal name",
  CASE WHEN t.typrelid != 0
      THEN CAST('tuple' AS pg_catalog.text)
    WHEN t.typlen < 0
      THEN CAST('var' AS pg_catalog.text)
    ELSE CAST(t.typlen AS pg_catalog.text)
  END AS "Size",
  pg_catalog.array_to_string(
      ARRAY(
          SELECT e.enumlabel
          FROM pg_catalog.pg_enum e
          WHERE e.enumtypid = t.oid
          ORDER BY e.enumsortorder
      ),
      E'\n'
  ) AS "Elements",
  pg_catalog.pg_get_userbyid(t.typowner) AS "Owner",
CASE WHEN pg_catalog.cardinality(t.typacl) = 0 THEN '(none)' ELSE
pg_catalog.array_to_string(t.typacl, E'\n') END AS "Access privileges",
    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 n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_type_is_visible(t.oid)
ORDER BY 1, 2;
                                       List of data types
 Schema | Name | Internal name | Size  | Elements |   Owner   | Access
privileges | Description
--------+------+---------------+-------+----------+-----------+-------------------+-------------
 public | test | test          | tuple |          | thombrown |
      |
(1 row)

Regards

Thom

---

From: Tom Lane <[email-masked]>
Date: Thu, 02 May 2024 10:15:29 -0400

> user types with extended output (\dt+):

If you want to look at what SQL psql issues for a \d-type command,
you don't even need to look at the server log.  Just start psql with
the -E (--echo-hidden) switch.

$ psql -E
psql (17devel)
Type "help" for help.

postgres=# \dT+
/******** QUERY *********/
SELECT n.nspname as "Schema",
  pg_catalog.format_type(t.oid, NULL) AS "Name",
  t.typname AS "Internal name",
  CASE WHEN t.typrelid != 0
      THEN CAST('tuple' AS pg_catalog.text)
    WHEN t.typlen < 0
      THEN CAST('var' AS pg_catalog.text)
    ELSE CAST(t.typlen AS pg_catalog.text)
  END AS "Size",
... etc etc ...

			regards, tom lane