indexes.ts48 lines · main

1	import { safeSql } from '../pg-format'
2
3	export const INDEXES_SQL = /* SQL */ safeSql`
4	SELECT
5	idx.indexrelid::int8 AS id,
6	idx.indrelid::int8 AS table_id,
7	n.nspname AS schema,
8	idx.indnatts AS number_of_attributes,
9	idx.indnkeyatts AS number_of_key_attributes,
10	idx.indisunique AS is_unique,
11	idx.indisprimary AS is_primary,
12	idx.indisexclusion AS is_exclusion,
13	idx.indimmediate AS is_immediate,
14	idx.indisclustered AS is_clustered,
15	idx.indisvalid AS is_valid,
16	idx.indcheckxmin AS check_xmin,
17	idx.indisready AS is_ready,
18	idx.indislive AS is_live,
19	idx.indisreplident AS is_replica_identity,
20	idx.indkey::smallint[] AS key_attributes,
21	idx.indcollation::integer[] AS collation,
22	idx.indclass::integer[] AS class,
23	idx.indoption::smallint[] AS options,
24	idx.indpred AS index_predicate,
25	obj_description(idx.indexrelid, 'pg_class') AS comment,
26	ix.indexdef as index_definition,
27	am.amname AS access_method,
28	jsonb_agg(
29	jsonb_build_object(
30	'attribute_number', a.attnum,
31	'attribute_name', a.attname,
32	'data_type', format_type(a.atttypid, a.atttypmod)
33	)
34	ORDER BY a.attnum
35	) AS index_attributes
36	FROM
37	pg_index idx
38	JOIN pg_class c ON c.oid = idx.indexrelid
39	JOIN pg_namespace n ON c.relnamespace = n.oid
40	JOIN pg_am am ON c.relam = am.oid
41	JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = ANY(idx.indkey)
42	JOIN pg_indexes ix ON c.relname = ix.indexname AND n.nspname = ix.schemaname
43	GROUP BY
44	idx.indexrelid, idx.indrelid, n.nspname, idx.indnatts, idx.indnkeyatts, idx.indisunique,
45	idx.indisprimary, idx.indisexclusion, idx.indimmediate, idx.indisclustered, idx.indisvalid,
46	idx.indcheckxmin, idx.indisready, idx.indislive, idx.indisreplident, idx.indkey,
47	idx.indcollation, idx.indclass, idx.indoption, idx.indexprs, idx.indpred, ix.indexdef, am.amname
48	`