public inbox for [email protected]  

help / color / mirror / Atom feed

From: Thom Brown <[email protected]>
To: Durumdara <[email protected]>
Cc: Postgres General <[email protected]>
Subject: Re: Listing only the user defined types (with owners)
Date: Thu, 2 May 2024 13:28:56 +0100
Message-ID: <CAA-aLv4sNe7NX3scGhGUvqiwf3cp7wJrYD6GYs8qM=45Vmu9VA@mail.gmail.com> (raw)
In-Reply-To: <CAEcMXhki9OkNTSzyKZp9ehPOeCCc3J7hiacA_7=jQi4WYywBaQ@mail.gmail.com>
References: <CAEcMXhki9OkNTSzyKZp9ehPOeCCc3J7hiacA_7=jQi4WYywBaQ@mail.gmail.com>

On Thu, 2 May 2024 at 12:40, Durumdara <[email protected]> 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!
>

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

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Listing only the user defined types (with owners)
  In-Reply-To: <CAA-aLv4sNe7NX3scGhGUvqiwf3cp7wJrYD6GYs8qM=45Vmu9VA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox