On Thu, 2 May 2024 at 12:40, Durumdara <durumdara@xxxxxxxxx> 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
