-- MigrateMyDatabase (migratemydatabase.eu): structure of this PostgreSQL database, for the free migration scan. -- READ-ONLY. It is one single SELECT on the system catalog (pg_catalog): no table data, no code text, nothing is -- created or changed. Views, functions, procedures and triggers are only measured (number of lines) and checked for -- keywords (such as EXECUTE or CURSOR); only the names of those keywords are output, never the code. -- -- How to run (PostgreSQL 11 to 18, also Amazon RDS / Aurora PostgreSQL, Azure, Cloud SQL, Supabase, Neon …): -- psql: psql "" -At -f mmdb-postgresql.sql -o mmdb-result.txt -- (connect to the database of your application; add -X if your ~/.psqlrc prints extra output) -- pgAdmin / DBeaver / DataGrip: open this file in a query window connected to the database of your application and -- run it. Then click the result grid, select all rows (Ctrl+A), copy (Ctrl+C) and paste them into the page. -- These tools may fetch only the first 200 or 1000 rows: fetch all rows first (DBeaver: "Fetch all rows"; -- pgAdmin: scroll to the end), or export the result to a file. The last line ("end") lets the page check that -- nothing is missing. -- -- Permissions: any role that can connect to the database (CONNECT). No superuser and no SELECT on tables is -- needed: PostgreSQL shows the catalog to every user. Run it as the owner or a superuser if you want exact sizes on -- services that hide them. Without CONNECT on the other databases only their number is counted. -- -- What is collected: version, encoding, collation and size of the database; schemas, tables (estimated row counts -- from the statistics, sizes), columns (types, defaults), indexes, keys, check constraints; for views, materialized -- views, functions, procedures, aggregates and triggers the number of lines and the features they use; extensions, -- sequences, types, partitioning, inheritance, foreign tables, row-level security, publications, subscriptions, -- event triggers, large objects (count only) and the number of login roles. Objects that belong to an extension -- (for example PostGIS) are left out. WITH RECURSIVE ns AS ( SELECT n.oid, n.nspname FROM pg_catalog.pg_namespace n WHERE n.nspname NOT IN ('pg_catalog', 'information_schema') AND n.nspname NOT LIKE 'pg\_toast%' AND n.nspname NOT LIKE 'pg\_temp%' AND NOT EXISTS (SELECT 1 FROM pg_catalog.pg_depend d WHERE d.classid = 'pg_catalog.pg_namespace'::regclass AND d.objid = n.oid AND d.deptype = 'e') ), rel AS ( SELECT c.oid, c.relname, c.relkind, c.relpersistence, c.relispartition, c.reltuples, c.relrowsecurity, ns.nspname FROM pg_catalog.pg_class c JOIN ns ON ns.oid = c.relnamespace WHERE c.relkind IN ('r', 'p', 'f', 'v', 'm', 'S', 'c') AND NOT EXISTS (SELECT 1 FROM pg_catalog.pg_depend d WHERE d.deptype = 'e' AND ((d.classid = 'pg_catalog.pg_class'::regclass AND d.objid = c.oid) OR (d.classid = 'pg_catalog.pg_type'::regclass AND d.objid = c.reltype))) ), tbl AS (SELECT * FROM rel WHERE relkind IN ('r', 'p', 'f')), -- partitioned tables: rows and size are the sum of all partitions ptree AS ( SELECT t.oid AS root, t.oid AS rel FROM tbl t WHERE t.relkind = 'p' UNION ALL SELECT pt.root, h.inhrelid FROM ptree pt JOIN pg_catalog.pg_inherits h ON h.inhparent = pt.rel ), tstat AS ( SELECT t.oid, CASE WHEN t.relkind = 'p' THEN (SELECT sum(greatest(c.reltuples, 0)) FROM ptree pt JOIN pg_catalog.pg_class c ON c.oid = pt.rel WHERE pt.root = t.oid AND c.relkind <> 'p') WHEN t.relkind = 'f' OR t.reltuples < 0 THEN NULL ELSE t.reltuples END AS rows, CASE WHEN t.relkind = 'p' THEN (SELECT sum(pg_catalog.pg_total_relation_size(pt.rel)) FROM ptree pt WHERE pt.root = t.oid) WHEN t.relkind = 'f' THEN 0 ELSE pg_catalog.pg_total_relation_size(t.oid) END AS bytes FROM tbl t ), -- domains (also domains over domains): the underlying base type and its type modifier dom AS ( SELECT t.oid, t.typbasetype AS base, t.typtypmod AS tm FROM pg_catalog.pg_type t WHERE t.typtype = 'd' UNION ALL SELECT d.oid, b.typbasetype, CASE WHEN d.tm >= 0 THEN d.tm ELSE b.typtypmod END FROM dom d JOIN pg_catalog.pg_type b ON b.oid = d.base AND b.typtype = 'd' ), dbase AS (SELECT d.oid, d.base, d.tm FROM dom d JOIN pg_catalog.pg_type b ON b.oid = d.base WHERE b.typtype <> 'd'), col AS ( SELECT t.nspname, t.relname, a.attname, a.attnum, ty.typname, a.attnotnull, a.attidentity, a.attcollation, to_jsonb(a) ->> 'attgenerated' AS gen, -- PostgreSQL 12+ (18: 'v' virtual) bt.typname AS basename, CASE WHEN a.atttypmod >= 0 THEN a.atttypmod ELSE coalesce(db.tm, -1) END AS tm, et.typcategory = 'A' AS is_array, st.typname AS sname, st.typtype AS stype, pg_catalog.pg_get_expr(ad.adbin, ad.adrelid) AS def FROM tbl t JOIN pg_catalog.pg_attribute a ON a.attrelid = t.oid AND a.attnum > 0 AND NOT a.attisdropped JOIN pg_catalog.pg_type ty ON ty.oid = a.atttypid LEFT JOIN dbase db ON db.oid = ty.oid LEFT JOIN pg_catalog.pg_type bt ON bt.oid = db.base JOIN pg_catalog.pg_type et ON et.oid = coalesce(db.base, ty.oid) -- effective type JOIN pg_catalog.pg_type st ON st.oid = CASE WHEN et.typcategory = 'A' THEN et.typelem ELSE et.oid END -- scalar type LEFT JOIN pg_catalog.pg_attrdef ad ON ad.adrelid = a.attrelid AND ad.adnum = a.attnum WHERE NOT t.relispartition -- partitions have exactly the columns of their parent ), fproc AS ( SELECT p.oid, p.proname, p.prokind, p.proretset, p.prorettype, p.prosecdef, p.prosrc, p.proargmodes, l.lanname, ns.nspname FROM pg_catalog.pg_proc p JOIN ns ON ns.oid = p.pronamespace JOIN pg_catalog.pg_language l ON l.oid = p.prolang WHERE NOT EXISTS (SELECT 1 FROM pg_catalog.pg_depend d WHERE d.classid = 'pg_catalog.pg_proc'::regclass AND d.objid = p.oid AND d.deptype = 'e') ), ftab AS ( SELECT t.oid, t.nspname, t.relname, s.srvname, '\m' || regexp_replace(lower(t.relname), '([^a-z0-9_])', '\\\1', 'g') || '\M' AS pat FROM tbl t JOIN pg_catalog.pg_foreign_table f ON f.ftrelid = t.oid JOIN pg_catalog.pg_foreign_server s ON s.oid = f.ftserver ), modsrc AS ( SELECT r.nspname, r.relname AS name, CASE r.relkind WHEN 'v' THEN 'V' ELSE 'MV' END AS typ, pg_catalog.pg_get_viewdef(r.oid) AS src, '' AS parent, '0' AS instead, '' AS pre FROM rel r WHERE r.relkind IN ('v', 'm') UNION ALL SELECT p.nspname, p.proname, CASE p.prokind WHEN 'p' THEN 'P' WHEN 'a' THEN 'AGG' ELSE CASE WHEN p.proretset THEN 'TF' ELSE 'FN' END END, CASE WHEN p.prokind = 'a' THEN NULL WHEN coalesce(p.prosrc, '') ~ '\S' THEN p.prosrc ELSE pg_catalog.pg_get_functiondef(p.oid) END, '', '0', CASE WHEN p.prokind = 'a' THEN '' ELSE 'LANG_' || p.lanname || ',' END || CASE WHEN p.proretset AND 't' = ANY (coalesce(p.proargmodes, '{}')) THEN 'RETURNS_TABLE,' WHEN p.proretset THEN 'RETURNS_SETOF,' ELSE '' END || CASE WHEN p.prorettype IN ('pg_catalog.trigger'::regtype, 'pg_catalog.event_trigger'::regtype) THEN 'TRIGGER_FN,' ELSE '' END || CASE WHEN p.prosecdef THEN 'SECURITY_DEFINER,' ELSE '' END FROM fproc p ), mods AS ( SELECT m.nspname, m.name, m.typ, m.parent, m.instead, CASE WHEN m.src IS NULL THEN '' ELSE (length(x.s) - length(replace(x.s, E'\n', '')) + 1)::text END AS lines, m.pre || CASE WHEN x.d ~ '\mexecute\s+(?!procedure\M|function\M)' THEN 'DYNSQL,' ELSE '' END || CASE WHEN x.d ~ '\m(cursor|refcursor)\M|\mfetch\s' THEN 'CURSOR,' ELSE '' END || CASE WHEN x.d ~ '\mexception\s+when\M' THEN 'EXCEPTION,' ELSE '' END || CASE WHEN x.d ~ '\mraise\M' THEN 'RAISE,' ELSE '' END || CASE WHEN x.d ~ '\mperform\M' THEN 'PERFORM,' ELSE '' END || CASE WHEN x.d ~ '\mreturning\M' THEN 'RETURNING,' ELSE '' END || CASE WHEN x.d ~ '(?>|#>' THEN 'JSON,' ELSE '' END || CASE WHEN x.d ~ '(^|[^~])~\*?($|[^~])|\mregexp_|\msimilar\s+to\M' THEN 'REGEX,' ELSE '' END || CASE WHEN x.d ~ '\milike\M|~~\*' THEN 'ILIKE,' ELSE '' END || CASE WHEN x.d ~ '\mlateral\M' THEN 'LATERAL,' ELSE '' END || CASE WHEN x.d ~ '\mdistinct\s+on\M' THEN 'DISTINCT_ON,' ELSE '' END || CASE WHEN x.d ~ '\mover\s*\(|\mwindow\M' THEN 'WINDOW,' ELSE '' END || CASE WHEN x.d ~ '\mwith\s+recursive\M' THEN 'RECURSIVE,' ELSE '' END || CASE WHEN x.d ~ '\mon\s+conflict\M' THEN 'UPSERT,' ELSE '' END || CASE WHEN x.d ~ '\mmerge\s+into\M' THEN 'MERGE,' ELSE '' END || CASE WHEN x.d ~ '\m(listen|notify|pg_notify)\M' THEN 'LISTEN_NOTIFY,' ELSE '' END || CASE WHEN x.d ~ '\mdblink' THEN 'DBLINK,' ELSE '' END || CASE WHEN x.d ~ '\mpostgres_fdw\M|\mimport\s+foreign\s+schema\M' OR EXISTS (SELECT 1 FROM ftab f WHERE x.d ~ f.pat) THEN 'FDW,' ELSE '' END || CASE WHEN x.d ~ '\mcopy\s' THEN 'COPY,' ELSE '' END || CASE WHEN x.d ~ '\mcreate\s+((global|local)\s+)?temp(orary)?\s+table\M' THEN 'TEMPTABLE,' ELSE '' END || CASE WHEN x.d ~ '\mpg_catalog\.|\minformation_schema\.|\mpg_(class|attribute|namespace|proc|type|index|constraint|depend|tables|views|indexes|roles|authid|database|settings|locks|stat_\w+)\M' THEN 'CATALOG,' ELSE '' END || CASE WHEN x.d ~ '\mpg_(try_)?advisory' THEN 'ADVISORY_LOCK,' ELSE '' END AS flags, x.d FROM modsrc m CROSS JOIN LATERAL (SELECT regexp_replace(m.src, '^\s+|\s+$', '', 'g') AS s, lower(coalesce(m.src, '')) AS d) x ), trg AS ( SELECT r.nspname, t.tgname, r.relname, (t.tgtype::int & 64 <> 0) AS instead, t.tgenabled FROM pg_catalog.pg_trigger t JOIN rel r ON r.oid = t.tgrelid WHERE NOT t.tgisinternal AND coalesce((to_jsonb(t) ->> 'tgparentid')::oid, 0) = 0 -- tgparentid: clones on partitions (13+) ), lines (s, o, k, f) AS ( SELECT 0, '', 'meta', ARRAY['postgresql', '1', to_char(now() AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS')] UNION ALL SELECT 1, '', 'db', ARRAY[d.datname::text, split_part(version(), E'\n', 1), split_part(current_setting('server_version'), ' ', 1), '', '', '', '', CASE coalesce(to_jsonb(d) ->> 'datlocprovider', 'c') WHEN 'i' THEN 'icu:' || coalesce(to_jsonb(d) ->> 'datlocale', to_jsonb(d) ->> 'daticulocale', '') WHEN 'b' THEN 'builtin:' || coalesce(to_jsonb(d) ->> 'datlocale', '') ELSE d.datcollate::text END, pg_catalog.pg_encoding_to_char(d.encoding)::text, round(pg_catalog.pg_database_size(d.oid) / 1048576.0, 1)::text, '', coalesce(current_setting('archive_mode', true), ''), '', (SELECT count(*) FROM pg_catalog.pg_database x WHERE NOT x.datistemplate AND x.datname NOT IN ('postgres', 'rdsadmin', 'azure_maintenance', 'azure_sys', 'cloudsqladmin', 'template0', 'template1'))::text] FROM pg_catalog.pg_database d WHERE d.datname = current_database() UNION ALL SELECT 2, n.nspname, 'schema', ARRAY[n.nspname::text, ((SELECT count(*) FROM rel r WHERE r.nspname = n.nspname AND r.relkind <> 'c') + (SELECT count(*) FROM fproc p WHERE p.nspname = n.nspname))::text] FROM ns n UNION ALL SELECT 3, t.nspname || chr(1) || t.relname, 'table', ARRAY[t.nspname::text, t.relname::text, round(s.rows)::bigint::text, round(coalesce(s.bytes, 0) / 1048576.0, 1)::text, (EXISTS (SELECT 1 FROM pg_catalog.pg_constraint c WHERE c.conrelid = t.oid AND c.contype = 'p'))::int::text, '0', '0', '0', (t.relkind = 'p')::int::text, CASE WHEN t.relkind = 'f' THEN 'foreign' WHEN t.relispartition THEN 'partition' WHEN EXISTS (SELECT 1 FROM pg_catalog.pg_inherits h WHERE h.inhrelid = t.oid) THEN 'inherits' WHEN t.relpersistence = 'u' THEN 'unlogged' ELSE '' END] FROM tbl t JOIN tstat s ON s.oid = t.oid UNION ALL SELECT 4, c.nspname || chr(1) || c.relname || chr(1) || lpad(c.attnum::text, 5, '0'), 'col', ARRAY[c.nspname::text, c.relname::text, c.attname::text, c.attnum::text, c.typname::text, coalesce(c.basename::text, ''), CASE WHEN c.sname IN ('varchar', 'bpchar') THEN CASE WHEN c.tm >= 4 THEN (c.tm - 4)::text ELSE '-1' END WHEN c.sname IN ('bit', 'varbit') THEN CASE WHEN c.tm >= 0 THEN c.tm::text ELSE '-1' END WHEN c.sname = 'name' THEN '63' WHEN c.sname IN ('text', 'citext', 'bytea', 'json', 'jsonb', 'xml', 'hstore', 'tsvector') THEN '-1' ELSE '' END, CASE WHEN c.sname = 'numeric' THEN CASE WHEN c.tm >= 4 THEN (((c.tm - 4) >> 16) & 65535)::text ELSE '' END WHEN c.sname = 'int2' THEN '5' WHEN c.sname = 'int4' THEN '10' WHEN c.sname = 'int8' THEN '19' WHEN c.sname = 'float4' THEN '24' WHEN c.sname = 'float8' THEN '53' WHEN c.sname = 'money' THEN '19' ELSE '' END, CASE WHEN c.sname = 'numeric' THEN CASE WHEN c.tm >= 4 THEN ((c.tm - 4) & 65535)::text ELSE '' END WHEN c.sname IN ('int2', 'int4', 'int8') THEN '0' WHEN c.sname = 'money' THEN '2' WHEN c.sname IN ('timestamp', 'timestamptz', 'time', 'timetz') THEN CASE WHEN c.tm >= 0 THEN c.tm::text ELSE '6' END WHEN c.sname = 'interval' THEN CASE WHEN c.tm >= 0 AND (c.tm & 65535) <> 65535 THEN (c.tm & 65535)::text ELSE '6' END ELSE '' END, (NOT c.attnotnull)::int::text, (c.attidentity IN ('a', 'd') OR coalesce(c.def, '') ~ '^nextval\(')::int::text, CASE coalesce(c.gen, '') WHEN 's' THEN '2' WHEN 'v' THEN '1' ELSE '0' END, coalesce(left(c.def, 200), ''), coalesce((SELECT co.collname::text FROM pg_catalog.pg_collation co WHERE co.oid = c.attcollation), ''), '0000' || c.is_array::int::text || (c.stype = 'e')::int::text || (c.sname IN ('lo', 'oid'))::int::text, 'U'] FROM col c UNION ALL SELECT 5, t.nspname || chr(1) || t.relname || chr(1) || ic.relname, 'index', ARRAY[t.nspname::text, t.relname::text, ic.relname::text, upper(am.amname) || CASE WHEN i.indexprs IS NOT NULL THEN ' EXPRESSION' ELSE '' END, i.indisunique::int::text, i.indisprimary::int::text, (EXISTS (SELECT 1 FROM pg_catalog.pg_constraint c WHERE c.conindid = i.indexrelid AND c.contype = 'u'))::int::text, (i.indpred IS NOT NULL)::int::text, (SELECT string_agg(replace(pg_catalog.pg_get_indexdef(i.indexrelid, k, true), ',', ';') || CASE WHEN (i.indoption[k - 1]::int & 1) = 1 THEN ' DESC' ELSE '' END, ',' ORDER BY k) FROM generate_series(1, i.indnkeyatts::int) k), (i.indnatts - i.indnkeyatts)::text] FROM pg_catalog.pg_index i JOIN tbl t ON t.oid = i.indrelid JOIN pg_catalog.pg_class ic ON ic.oid = i.indexrelid JOIN pg_catalog.pg_am am ON am.oid = ic.relam WHERE NOT EXISTS (SELECT 1 FROM pg_catalog.pg_inherits h WHERE h.inhrelid = i.indexrelid) -- partition copies of an index UNION ALL SELECT 6, t.nspname || chr(1) || t.relname || chr(1) || c.conname, 'fk', ARRAY[t.nspname::text, t.relname::text, c.conname::text, rn.nspname::text, rc.relname::text, CASE c.confdeltype WHEN 'c' THEN 'CASCADE' WHEN 'n' THEN 'SET_NULL' WHEN 'd' THEN 'SET_DEFAULT' WHEN 'r' THEN 'RESTRICT' ELSE 'NO_ACTION' END, CASE c.confupdtype WHEN 'c' THEN 'CASCADE' WHEN 'n' THEN 'SET_NULL' WHEN 'd' THEN 'SET_DEFAULT' WHEN 'r' THEN 'RESTRICT' ELSE 'NO_ACTION' END, (EXISTS (SELECT 1 FROM pg_catalog.pg_trigger g WHERE g.tgconstraint = c.oid AND g.tgenabled = 'D'))::int::text, (NOT c.convalidated)::int::text, (SELECT string_agg(a.attname::text, ',' ORDER BY k.i) FROM unnest(c.conkey) WITH ORDINALITY k(n, i) JOIN pg_catalog.pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = k.n), (SELECT string_agg(a.attname::text, ',' ORDER BY k.i) FROM unnest(c.confkey) WITH ORDINALITY k(n, i) JOIN pg_catalog.pg_attribute a ON a.attrelid = c.confrelid AND a.attnum = k.n)] FROM pg_catalog.pg_constraint c JOIN tbl t ON t.oid = c.conrelid JOIN pg_catalog.pg_class rc ON rc.oid = c.confrelid JOIN pg_catalog.pg_namespace rn ON rn.oid = rc.relnamespace WHERE c.contype = 'f' AND c.conparentid = 0 UNION ALL SELECT 7, t.nspname || chr(1) || t.relname || chr(1) || c.conname, 'check', ARRAY[t.nspname::text, t.relname::text, c.conname::text, left(regexp_replace(pg_catalog.pg_get_constraintdef(c.oid, true), '^CHECK ', ''), 400), '0'] FROM pg_catalog.pg_constraint c JOIN tbl t ON t.oid = c.conrelid WHERE c.contype = 'c' AND c.conislocal UNION ALL SELECT 8, m.typ || chr(1) || m.nspname || chr(1) || m.name, 'module', ARRAY[m.nspname::text, m.name::text, m.typ, m.lines, m.parent, m.instead, m.flags] FROM mods m UNION ALL SELECT 9, t.nspname || chr(1) || t.relname || chr(1) || t.tgname, 'module', ARRAY[t.nspname::text, t.tgname::text, 'TR', '1', t.relname::text, t.instead::int::text, CASE WHEN t.tgenabled = 'D' THEN 'DISABLED,' ELSE '' END] FROM trg t UNION ALL SELECT 10, m.nspname || chr(1) || m.name, 'xref', ARRAY[m.nspname::text, m.name::text, 'dblink', '', '', ''] FROM mods m WHERE m.d ~ '\mdblink' UNION ALL SELECT 10, m.nspname || chr(1) || m.name || chr(1) || f.nspname || chr(1) || f.relname, 'xref', ARRAY[m.nspname::text, m.name::text, 'fdw', '', f.nspname::text, f.relname::text, f.srvname::text] FROM mods m JOIN ftab f ON m.d ~ f.pat UNION ALL SELECT 11, e.extname, 'feature', ARRAY['extension:' || e.extname, '1', e.extversion::text] FROM pg_catalog.pg_extension e UNION ALL SELECT 12, q.key, 'feature', ARRAY[q.key, q.n::text, coalesce(q.extra, '')] FROM ( SELECT 'a' AS o, 'sequence' AS key, count(*) AS n, sum(CASE WHEN NOT EXISTS (SELECT 1 FROM pg_catalog.pg_depend d WHERE d.classid = 'pg_catalog.pg_class'::regclass AND d.objid = r.oid AND d.deptype IN ('a', 'i')) THEN 1 ELSE 0 END)::text AS extra FROM rel r WHERE r.relkind = 'S' UNION ALL SELECT 'b', 'domain', count(*), NULL FROM pg_catalog.pg_type t JOIN ns ON ns.oid = t.typnamespace WHERE t.typtype = 'd' AND NOT EXISTS (SELECT 1 FROM pg_catalog.pg_depend d WHERE d.classid = 'pg_catalog.pg_type'::regclass AND d.objid = t.oid AND d.deptype = 'e') UNION ALL SELECT 'c', 'enum_type', count(*), NULL FROM pg_catalog.pg_type t JOIN ns ON ns.oid = t.typnamespace WHERE t.typtype = 'e' AND NOT EXISTS (SELECT 1 FROM pg_catalog.pg_depend d WHERE d.classid = 'pg_catalog.pg_type'::regclass AND d.objid = t.oid AND d.deptype = 'e') UNION ALL SELECT 'd', 'composite_type', count(*), NULL FROM rel r WHERE r.relkind = 'c' UNION ALL SELECT 'e', 'materialized_view', count(*), NULL FROM rel r WHERE r.relkind = 'm' UNION ALL SELECT 'f', 'foreign_table', count(*), NULL FROM rel r WHERE r.relkind = 'f' UNION ALL SELECT 'g', 'foreign_server', count(*), NULL FROM pg_catalog.pg_foreign_server s WHERE NOT EXISTS (SELECT 1 FROM pg_catalog.pg_depend d WHERE d.classid = 'pg_catalog.pg_foreign_server'::regclass AND d.objid = s.oid AND d.deptype = 'e') UNION ALL SELECT 'h', 'partitioned_table', count(*), NULL FROM rel r WHERE r.relkind = 'p' UNION ALL SELECT 'i', 'inheritance', count(*), NULL FROM rel r WHERE r.relkind IN ('r', 'f', 'p') AND NOT r.relispartition AND EXISTS (SELECT 1 FROM pg_catalog.pg_inherits h WHERE h.inhrelid = r.oid) UNION ALL SELECT 'j', 'rls_policy', (SELECT count(*) FROM pg_catalog.pg_policy p JOIN rel r ON r.oid = p.polrelid), (SELECT count(*) FROM rel r WHERE r.relrowsecurity)::text UNION ALL SELECT 'k', 'publication', count(*), NULL FROM pg_catalog.pg_publication UNION ALL SELECT 'l', 'subscription', count(*), NULL FROM pg_catalog.pg_subscription s WHERE s.subdbid = (SELECT oid FROM pg_catalog.pg_database WHERE datname = current_database()) UNION ALL SELECT 'm', 'event_trigger', count(*), NULL FROM pg_catalog.pg_event_trigger e WHERE NOT EXISTS (SELECT 1 FROM pg_catalog.pg_depend d WHERE d.classid = 'pg_catalog.pg_event_trigger'::regclass AND d.objid = e.oid AND d.deptype = 'e') UNION ALL SELECT 'n', 'large_object', count(*), NULL FROM pg_catalog.pg_largeobject_metadata UNION ALL SELECT 'o', 'unlogged_table', count(*), NULL FROM rel r WHERE r.relkind IN ('r', 'p') AND r.relpersistence = 'u' UNION ALL SELECT 'p', 'tablespace', count(*), NULL FROM pg_catalog.pg_tablespace WHERE spcname NOT IN ('pg_default', 'pg_global') UNION ALL SELECT 'q', 'role', count(*), sum(CASE WHEN rolsuper THEN 1 ELSE 0 END)::text FROM pg_catalog.pg_roles WHERE rolcanlogin AND rolname !~ '^(pg_|rds|azure|cloudsql)' UNION ALL SELECT 'r', 'exclusion_constraint', count(*), NULL FROM pg_catalog.pg_constraint c JOIN tbl t ON t.oid = c.conrelid WHERE c.contype = 'x' AND c.conislocal UNION ALL SELECT 's', 'rule', count(*), NULL FROM pg_catalog.pg_rewrite w JOIN tbl t ON t.oid = w.ev_class WHERE w.rulename <> '_RETURN' ) q ), numbered AS ( SELECT row_number() OVER (ORDER BY l.s, l.o COLLATE "C") AS n, 'MMDB1|' || l.k || coalesce((SELECT string_agg('|' || replace(replace(replace(coalesce(u.v, ''), '|', '/'), E'\r', ' '), E'\n', ' '), '' ORDER BY u.i) FROM unnest(l.f) WITH ORDINALITY u(v, i)), '') AS line FROM lines l ) SELECT line AS mmdb FROM ( SELECT n, line FROM numbered UNION ALL SELECT (SELECT count(*) FROM numbered) + 1, 'MMDB1|end|' || (SELECT count(*) FROM numbered) ) z ORDER BY n;