sorry... SELECT COALESCE(Json_agg(Row_to_json(info)), '[]' :: JSON) AS TABLES FROM (WITH partitions AS (SELECT array (WITH partitioned_tables AS (SELECT array (SELECT oid FROM pg_class WHERE relkind = 'p') AS parent_tables) SELECT child.relname AS PARTITION FROM partitioned_tables, pg_inherits JOIN pg_class child ON pg_inherits.inhrelid = child.oid JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace WHERE ((nmsp_child.nspname='servicedesk')) AND pg_inherits.inhparent = ANY (partitioned_tables.parent_tables) ) AS NAMES) SELECT pgn.nspname AS table_schema,
pgc.relname AS TABLE_NAME, CASE WHEN pgc.relkind = 'r' THEN 'TABLE' WHEN pgc.relkind = 'f' THEN 'FOREIGN TABLE' WHEN pgc.relkind = 'v' THEN 'VIEW' WHEN pgc.relkind = 'm' THEN 'MATERIALIZED VIEW' WHEN pgc.relkind = 'p' THEN 'PARTITIONED TABLE' END AS table_type, obj_description(pgc.oid) AS COMMENT, COALESCE(json_agg(DISTINCT row_to_json(isc) :: JSONB || jsonb_build_object('comment', col_description(pga.attrelid, pga.attnum))) filter ( WHERE isc.column_name IS NOT NULL), '[]' :: JSON) AS columns, COALESCE(json_agg(DISTINCT row_to_json(ist) :: JSONB || jsonb_build_object('comment', obj_description(pgt.oid))) filter ( WHERE ist.trigger_name IS NOT NULL), '[]' :: JSON) AS TRIGGERS, row_to_json(isv) AS view_info FROM partitions, pg_class AS pgc INNER JOIN pg_namespace AS pgn ON pgc.relnamespace = pgn.oid /* columns */ /* This is a simplified version of how information_schema.columns was ** implemented in postgres 9.5, but modified to support materialized ** views. */ LEFT OUTER JOIN pg_attribute AS pga ON pga.attrelid = pgc.oid LEFT OUTER JOIN (SELECT nc.nspname AS table_schema, c.relname AS TABLE_NAME, a.attname AS COLUMN_NAME, a.attnum AS ordinal_position, pg_get_expr(ad.adbin, ad.adrelid) AS column_default, CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END AS is_nullable, CASE WHEN t.typtype = 'd' THEN CASE WHEN bt.typelem <> 0 AND bt.typlen = -1 THEN 'ARRAY' WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, NULL) ELSE 'USER-DEFINED' END ELSE CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY' WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, NULL) ELSE 'USER-DEFINED' END END AS data_type, coalesce(bt.typname, t.typname) AS data_type_name, CASE WHEN a.attidentity = 'd' THEN TRUE WHEN a.attidentity = 'a' THEN TRUE ELSE FALSE END AS is_identity FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum) JOIN (pg_class c JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON a.attrelid = c.oid JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON a.atttypid = t.oid LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid)) ON (t.typtype = 'd' AND t.typbasetype = bt.oid) LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid)) ON a.attcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default') WHERE (NOT pg_is_other_temp_schema(nc.oid)) AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v', 'm', 'f', 'p') AND (pg_has_role(c.relowner, 'USAGE') OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES')) ) AS isc ON isc.table_schema = pgn.nspname AND isc.table_name = pgc.relname AND isc.column_name = pga.attname /* triggers */ LEFT OUTER JOIN pg_trigger AS pgt ON pgt.tgrelid = pgc.oid LEFT OUTER JOIN information_schema.triggers AS ist ON ist.event_object_schema = pgn.nspname AND ist.event_object_table = pgc.relname AND ist.trigger_name = pgt.tgname /* This is a simplified version of how information_schema.views was ** implemented in postgres 9.5, but modified to support materialized ** views. */ LEFT OUTER JOIN (SELECT nc.nspname AS table_schema, c.relname AS TABLE_NAME, CASE WHEN pg_has_role(c.relowner, 'USAGE') THEN pg_get_viewdef(c.oid) ELSE NULL END AS view_definition, CASE WHEN pg_relation_is_updatable(c.oid, FALSE) & 20 = 20 THEN 'YES' ELSE 'NO' END AS is_updatable, CASE WHEN pg_relation_is_updatable(c.oid, FALSE) & 8 = 8 THEN 'YES' ELSE 'NO' END AS is_insertable_into, CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 81 = 81) THEN 'YES' ELSE 'NO' END AS is_trigger_updatable, CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 73 = 73) THEN 'YES' ELSE 'NO' END AS is_trigger_deletable, CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 69 = 69) THEN 'YES' ELSE 'NO' END AS is_trigger_insertable_into FROM pg_namespace nc, pg_class c WHERE c.relnamespace = nc.oid AND c.relkind in ('v', 'm') AND (NOT pg_is_other_temp_schema(nc.oid)) AND (pg_has_role(c.relowner, 'USAGE') OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES')) ) AS isv ON isv.table_schema = pgn.nspname AND isv.table_name = pgc.relname WHERE pgc.relkind IN ('r', 'v', 'f', 'm', 'p') AND ((pgn.nspname='servicedesk')) GROUP BY pgc.oid, pgn.nspname, pgc.relname, table_type, isv.*) AS info; On Tue, 21 May 2024 at 13:14, Laurenz Albe <laurenz.a...@cybertec.at> wrote: > On Tue, 2024-05-21 at 12:49 +0200, Sašo Gantar wrote: > > thanks for the info, but is there any solution, given that it's system > tables? > > We still don't know the query. > > Yours, > Laurenz Albe >