MIME-Version: 1.0
References: <CAEcMXhki9OkNTSzyKZp9ehPOeCCc3J7hiacA_7=jQi4WYywBaQ@mail.gmail.com>
In-Reply-To: <CAEcMXhki9OkNTSzyKZp9ehPOeCCc3J7hiacA_7=jQi4WYywBaQ@mail.gmail.com>
From: Thom Brown <thom@linux.com>
Date: Thu, 2 May 2024 13:28:56 +0100
Message-ID: <CAA-aLv4sNe7NX3scGhGUvqiwf3cp7wJrYD6GYs8qM=45Vmu9VA@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="000000000000ce1201061777c030"
Archived-At: <https://www.postgresql.org/message-id/CAA-aLv4sNe7NX3scGhGUvqiwf3cp7wJrYD6GYs8qM%3D45Vmu9VA%40mail.gmail.com>
Precedence: bulk

--000000000000ce1201061777c030
Content-Type: text/plain; charset="UTF-8"

On Thu, 2 May 2024 at 12:40, 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!
>

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

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, 2 May 2024 at 12:40, Durumdara &l=
t;<a href=3D"mailto:durumdara@gmail.com">durumdara@gmail.com</a>&gt; wrote:=
</div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D=
"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-le=
ft:1ex"><div dir=3D"ltr">Hello!<div><br></div><div>I have a script which ca=
n 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"monosp=
ace">=C2=A0 =C2=A0 FOR r IN SELECT tablename FROM pg_tables WHERE (schemana=
me =3D &#39;public&#39;) and (tableowner &lt;&gt; act_dbowner)<br>=C2=A0 =
=C2=A0 LOOP<br></font></div><div>...</div><div><br></div><div>For types I f=
ound pg_type, but this contains all types.</div><div><br></div><div>For exa=
mple I have only one user defined type, like &quot;T_TEST&quot;, but this p=
g_type relation contains the basic data types,=C2=A0 other data types, from=
 any schema.</div><div><br></div><div>Do you have a working Query which lis=
ts the user defined types with the owners?</div><div><br></div><div>Thank y=
ou for=C2=A0your help!</div></div></blockquote><div><br></div><div>You can =
always cheat and copy what psql does when you tell it to list all user type=
s with extended output (\dt+):</div><div><br>postgres=3D# SET log_min_durat=
ion_statement =3D 0;<br>SET<br>postgres=3D# SET client_min_messages TO LOG;=
<br>LOG: =C2=A0duration: 0.137 ms =C2=A0statement: SET client_min_messages =
TO LOG;<br>SET<br>postgres=3D# \dT+<br>LOG: =C2=A0duration: 2.901 ms =C2=A0=
statement: SELECT n.nspname as &quot;Schema&quot;,<br>=C2=A0 pg_catalog.for=
mat_type(t.oid, NULL) AS &quot;Name&quot;,<br>=C2=A0 t.typname AS &quot;Int=
ernal name&quot;,<br>=C2=A0 CASE WHEN t.typrelid !=3D 0<br>=C2=A0 =C2=A0 =
=C2=A0 THEN CAST(&#39;tuple&#39; AS pg_catalog.text)<br>=C2=A0 =C2=A0 WHEN =
t.typlen &lt; 0<br>=C2=A0 =C2=A0 =C2=A0 THEN CAST(&#39;var&#39; AS pg_catal=
og.text)<br>=C2=A0 =C2=A0 ELSE CAST(t.typlen AS pg_catalog.text)<br>=C2=A0 =
END AS &quot;Size&quot;,<br>=C2=A0 pg_catalog.array_to_string(<br>=C2=A0 =
=C2=A0 =C2=A0 ARRAY(<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 SELECT e.enumlab=
el<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 FROM pg_catalog.pg_enum e<br>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WHERE e.enumtypid =3D t.oid<br>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 ORDER BY e.enumsortorder<br>=C2=A0 =C2=A0 =C2=A0 )=
,<br>=C2=A0 =C2=A0 =C2=A0 E&#39;\n&#39;<br>=C2=A0 ) AS &quot;Elements&quot;=
,<br>=C2=A0 pg_catalog.pg_get_userbyid(t.typowner) AS &quot;Owner&quot;,<br=
>CASE WHEN pg_catalog.cardinality(t.typacl) =3D 0 THEN &#39;(none)&#39; ELS=
E pg_catalog.array_to_string(t.typacl, E&#39;\n&#39;) END AS &quot;Access p=
rivileges&quot;,<br>=C2=A0 =C2=A0 pg_catalog.obj_description(t.oid, &#39;pg=
_type&#39;) as &quot;Description&quot;<br>FROM pg_catalog.pg_type t<br>=C2=
=A0 =C2=A0 =C2=A0LEFT JOIN pg_catalog.pg_namespace n ON n.oid =3D t.typname=
space<br>WHERE (t.typrelid =3D 0 OR (SELECT c.relkind =3D &#39;c&#39; FROM =
pg_catalog.pg_class c WHERE c.oid =3D t.typrelid))<br>=C2=A0 AND NOT EXISTS=
(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid =3D t.typelem AND el.typa=
rray =3D t.oid)<br>=C2=A0 =C2=A0 =C2=A0 AND n.nspname &lt;&gt; &#39;pg_cata=
log&#39;<br>=C2=A0 =C2=A0 =C2=A0 AND n.nspname &lt;&gt; &#39;information_sc=
hema&#39;<br>=C2=A0 AND pg_catalog.pg_type_is_visible(t.oid)<br>ORDER BY 1,=
 2;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Li=
st of data types<br>=C2=A0Schema | Name | Internal name | Size =C2=A0| Elem=
ents | =C2=A0 Owner =C2=A0 | Access privileges | Description <br>--------+-=
-----+---------------+-------+----------+-----------+-------------------+--=
-----------<br>=C2=A0public | test | test =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0| tuple | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| thombrown | =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | <br>(1 row)<br>=C2=A0</d=
iv></div><div>Regards</div><div><br></div><div><div dir=3D"ltr" class=3D"gm=
ail_signature">Thom</div></div></div>

--000000000000ce1201061777c030--