我根据@Erwin Brandstetter 的答案提出了以下查询来获取表名称、index_type、operator_classes 和索引的唯一性:
SELECT i.indrelid::regclass::text AS table
, c.relname AS index_name
, a.amname AS index_type
, opc.operator_classes
, i.indisunique AS is_unique
, array_agg(att.attname) as column_names
FROM pg_catalog.pg_namespace n
JOIN pg_catalog.pg_class c ON c.relnamespace = n.oid
JOIN pg_catalog.pg_attribute att ON att.attrelid = c.oid
JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
JOIN pg_catalog.pg_am a ON a.oid = c.relam
CROSS JOIN LATERAL (
SELECT ARRAY (SELECT opc.opcname
FROM unnest(i.indclass::oid[]) WITH ORDINALITY o(oid, ord)
JOIN pg_opclass opc ON opc.oid = o.oid
ORDER BY o.ord)
) opc(operator_classes)
WHERE n.nspname !~ '^pg_'
AND c.relkind = ANY (ARRAY['r', 't', 'i'])
group by
i.indrelid, c.relname, a.amname, opc.operator_classes, i.indisunique
ORDER BY 1, 2, 3, 4;
这非常有效,只是当通过转换另一列的数据创建索引时它会返回错误的列名称。例如,具有以下索引:
CREATE INDEX _ixtr_luxury_inventory_images ON public.luxury_inventory
USING gin (f_textarr2text(images) gin_trgm_ops)
返回:
table | luxury_inventory
index_name | _ixtr_luxury_inventory_images
index_type | gin
operator_classes | ['gin_trgm_ops']
is_unique | False
column_names | ['f_textarr2text']
我们得到f_textarr2text
作为列名称而不是images
。
我们怎样才能得到正确的column_names?如果这很重要的话,我正在使用 Postgres 16.2。
更新:这是建表SQL:
create table "luxury_inventory" (
id bigserial primary key,
images text[])
CREATE OR REPLACE FUNCTION "public".f_textarr2text(text[])
RETURNS text LANGUAGE sql IMMUTABLE AS $$SELECT array_to_string($1, ',')$$;
想一想这个问题,也许比从第一个答案中指出的列
pg_get_indexdef
中取出列序号更好indexprs
,特别是从该varattno
字段中取出列序号。regexp_matches
在 'indexprs' (转换pg_tree_node
为text
) forvarattno
)我将以下内容放在一起,回到表中
columns
的定义information_schema
:并产生:
我觉得这更符合我们双方的愿望。
注意:
attname
,因为这确实是我所需要的JSON_AGG(columns.column_name)
可以替换为JSON_AGG(columns.*)
等来选择整行作为列。ANY (ARRAY['r', 't', 'i']
已更新为IN ANY ('r', 't', 'i')
编辑:
为了将其放入单个列中,
COALESCE
每当新函数返回时NULL
,我们将插入一个包含 的结果的数组pg_get_indexdef
,并且更ARRAY_AGG
喜欢JSON_AGG
。全部一起:这会产生:
尝试一下
pg_get_indexdef
,像这样:人们应该记得,一个索引可以引用多个列,因此一个函数也可以需要多个列作为参数。
也就是说,通过检索
pg_get_indexdef
和应用一些正则表达式,我们可以获得我们的参数其中只有用函数调用定义的索引才会在此处返回行:
最终,您将返回函数名称,因为该函数调用用于构建索引,而不仅仅是基础列。
查看
pg_catalog.pg_index.indexprs
查询中的列,您将看到类似以下内容的内容,其中公开了函数的索引方式: