Обсуждение: Listing only the user defined types (with owners)
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
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
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
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 <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!Best regardsdd
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
Thom Brown <thom@linux.com> writes:
> On Thu, 2 May 2024 at 12:40, Durumdara <durumdara@gmail.com> wrote:
>> Do you have a working Query which lists the user defined types with the
>> owners?
> You can always cheat and copy what psql does when you tell it to list all
> user types with extended output (\dt+):
If you want to look at what SQL psql issues for a \d-type command,
you don't even need to look at the server log. Just start psql with
the -E (--echo-hidden) switch.
$ psql -E
psql (17devel)
Type "help" for help.
postgres=# \dT+
/******** QUERY *********/
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",
... etc etc ...
regards, tom lane