public inbox for [email protected]  

help / color / mirror / Atom feed

From: Kashif Zeeshan <[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 17:06:35 +0500
Message-ID: <CAAPsdhf42kFkq6ZeuX1q+qW=H7XaxdiBYzWCooMnWspM=4s8_w@mail.gmail.com> (raw)
In-Reply-To: <CAEcMXhki9OkNTSzyKZp9ehPOeCCc3J7hiacA_7=jQi4WYywBaQ@mail.gmail.com>
References: <CAEcMXhki9OkNTSzyKZp9ehPOeCCc3J7hiacA_7=jQi4WYywBaQ@mail.gmail.com>

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 <[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!
>
> Best regards
> dd
>
>
>

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: <CAAPsdhf42kFkq6ZeuX1q+qW=H7XaxdiBYzWCooMnWspM=4s8_w@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