MIME-Version: 1.0
References: <CAEcMXhki9OkNTSzyKZp9ehPOeCCc3J7hiacA_7=jQi4WYywBaQ@mail.gmail.com>
In-Reply-To: <CAEcMXhki9OkNTSzyKZp9ehPOeCCc3J7hiacA_7=jQi4WYywBaQ@mail.gmail.com>
From: Kashif Zeeshan <kashi.zeeshan@gmail.com>
Date: Thu, 2 May 2024 17:06:35 +0500
Message-ID: <CAAPsdhf42kFkq6ZeuX1q+qW=H7XaxdiBYzWCooMnWspM=4s8_w@mail.gmail.com>
Subject: Re: Listing only the user defined types (with owners)
To: Durumdara <durumdara@gmail.com>
Cc: Postgres General <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f97c900617776fa8"
Archived-At: <https://www.postgresql.org/message-id/CAAPsdhf42kFkq6ZeuX1q%2BqW%3DH7XaxdiBYzWCooMnWspM%3D4s8_w%40mail.gmail.com>
Precedence: bulk

--000000000000f97c900617776fa8
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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 =3D pg_type.typnamespace
WHERE
typtype =3D '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=E2=80=AFPM Durumdara <durumdara@gmail.com> wrot=
e:

> 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 =3D 'publi=
c')
> 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 a=
ny
> schema.
>
> Do you have a working Query which lists the user defined types with the
> owners?
>
> Thank you for your help!
>
> Best regards
> dd
>
>
>

--000000000000f97c900617776fa8
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi<div><br></div><div>You can find all user defined types =
with the following query.</div><div><br></div><div>CREATE TYPE bug_status A=
S ENUM (&#39;new&#39;, &#39;open&#39;, &#39;closed&#39;);<br></div><div><br=
></div><div>SELECT typname<br>FROM pg_catalog.pg_type<br>=C2=A0 JOIN pg_cat=
alog.pg_namespace<br>=C2=A0 ON pg_namespace.oid =3D pg_type.typnamespace<br=
>WHERE<br>typtype =3D &#39;e&#39; and nspname NOT IN (&#39;pg_catalog&#39;,=
 &#39;information_schema&#39;);<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 typname<br>-=
----------------------<br>=C2=A0bug_status<br></div><div><br></div><div>The=
 values for=C2=A0typtype=C2=A0are as follows</div><div>typtype is b for a b=
ase type, c for a composite type (e.g., a table&#39;s row type), d for a do=
main, e for an enum type, p for a pseudo-type, or r for a range type. See a=
lso typrelid and typbasetype.<br></div><div><br></div><div><br></div><div>R=
egards</div><div>Kashif Zeeshan</div><div>Bitnine Global</div></div><br><di=
v class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, May 2=
, 2024 at 4:40=E2=80=AFPM Durumdara &lt;<a href=3D"mailto:durumdara@gmail.c=
om">durumdara@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_=
quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,=
204);padding-left:1ex"><div dir=3D"ltr">Hello!<div><br></div><div>I have a =
script which can change the table owners to the database owner.</div><div><=
br></div><div>I select the tables like this:</div><div><br></div><div><font=
 face=3D"monospace">=C2=A0 =C2=A0 FOR r IN SELECT tablename FROM pg_tables =
WHERE (schemaname =3D &#39;public&#39;) and (tableowner &lt;&gt; act_dbowne=
r)<br>=C2=A0 =C2=A0 LOOP<br></font></div><div>...</div><div><br></div><div>=
For types I found pg_type, but this contains all types.</div><div><br></div=
><div>For example I have only one user defined type, like &quot;T_TEST&quot=
;, but this pg_type relation contains the basic data types,=C2=A0 other dat=
a types, from any schema.</div><div><br></div><div>Do you have a working Qu=
ery which lists the user defined types with the owners?</div><div><br></div=
><div>Thank you for=C2=A0your help!</div><div><br></div><div>Best regards</=
div><div>dd</div><div><br></div><div><br></div></div>
</blockquote></div>

--000000000000f97c900617776fa8--