gistfile1.txt

CREATE MATERIALIZED VIEW  public.db_info_mat AS
SELECT json_object_agg(pg_namespace.nspname, json_build_object('name', pg_namespace.nspname, 'oid', pg_namespace.oid, 'tables', ( SELECT json_object_agg(pg_class.relname, json_build_object('name', pg_class.relname, 'oid', pg_class.oid, 'primary_key', ( SELECT pg_attribute.attname
                   FROM pg_index
                     JOIN pg_attribute pg_attribute ON pg_attribute.attrelid = pg_index.indrelid AND (pg_attribute.attnum = ANY (pg_index.indkey::smallint[]))
                  WHERE pg_index.indrelid = pg_class.oid AND pg_index.indisprimary AND pg_index.indkey::text = '1'::text
                 LIMIT 1), 'columns', ( SELECT json_agg(json_build_object('index', pg_attribute.attnum, 'name', pg_attribute.attname, 'type_schema_name', pg_namespace_type.nspname, 'type_name', pg_type.typname, 'enum_values', ( SELECT array_agg(pg_enum.enumlabel ORDER BY pg_enum.enumsortorder) AS array_agg
                           FROM pg_enum
                          WHERE pg_enum.enumtypid = pg_type.oid), 'nullable', pg_attribute.attnotnull = false, 'foreign_key', ( SELECT json_build_object('schema', pg_namespace_ref.nspname, 'table', pg_class_ref.relname, 'column', pg_attribute_ref.attname, 'on_update',
                                CASE pg_constraint.confupdtype
                                    WHEN 'a'::"char" THEN 'no action'::text
                                    WHEN 'r'::"char" THEN 'restrict'::text
                                    WHEN 'c'::"char" THEN 'cascade'::text
                                    WHEN 'n'::"char" THEN 'set null'::text
                                    WHEN 'd'::"char" THEN 'set default'::text
                                    ELSE NULL::text
                                END, 'on_delete',
                                CASE pg_constraint.confdeltype
                                    WHEN 'a'::"char" THEN 'no action'::text
                                    WHEN 'r'::"char" THEN 'restrict'::text
                                    WHEN 'c'::"char" THEN 'cascade'::text
                                    WHEN 'n'::"char" THEN 'set null'::text
                                    WHEN 'd'::"char" THEN 'set default'::text
                                    ELSE NULL::text
                                END, 'match_type',
                                CASE pg_constraint.confmatchtype
                                    WHEN 'f'::"char" THEN 'full'::text
                                    WHEN 'p'::"char" THEN 'partial'::text
                                    WHEN 's'::"char" THEN 'simple'::text
                                    ELSE NULL::text
                                END) AS json_build_object
                           FROM pg_constraint
                             JOIN pg_attribute pg_attribute_fk ON pg_attribute_fk.attrelid = pg_constraint.conrelid AND pg_attribute_fk.attnum = pg_constraint.conkey[1]
                             JOIN pg_class pg_class_ref ON pg_class_ref.oid = pg_constraint.confrelid
                             JOIN pg_namespace pg_namespace_ref ON pg_namespace_ref.oid = pg_class_ref.relnamespace
                             JOIN pg_attribute pg_attribute_ref ON pg_attribute_ref.attrelid = pg_class_ref.oid AND pg_attribute_ref.attnum = pg_constraint.confkey[1]
                             JOIN pg_index pg_index_ref ON pg_index_ref.indrelid = pg_class_ref.oid AND pg_index_ref.indisprimary AND pg_attribute_ref.attnum = pg_index_ref.indkey[0]
                          WHERE pg_constraint.conrelid = pg_class.oid AND pg_attribute_fk.attnum = pg_attribute.attnum AND pg_constraint.contype = 'f'::"char" AND array_length(pg_constraint.conkey, 1) = 1 AND array_length(pg_constraint.confkey, 1) = 1)) ORDER BY pg_attribute.attnum) AS json_agg
                   FROM pg_attribute
                     JOIN pg_type ON pg_attribute.atttypid = pg_type.oid
                     JOIN pg_namespace pg_namespace_type ON pg_type.typnamespace = pg_namespace_type.oid
                  WHERE pg_attribute.attnum > 0 AND NOT pg_attribute.attisdropped AND pg_attribute.attrelid = pg_class.oid)) ORDER BY pg_class.relname) AS json_object_agg
           FROM pg_class
          WHERE pg_class.relkind = 'r'::"char" AND pg_class.relnamespace = pg_namespace.oid)) ORDER BY pg_namespace.nspname)::text AS db_info
   FROM pg_namespace
  WHERE NOT pg_is_other_temp_schema(pg_namespace.oid) AND NOT pg_namespace.nspname ~~ 'pg_%'::text AND NOT pg_namespace.nspname = 'information_schema'::name;