我維護Beekeeper Studio，它的功能之一是顯示索引資訊。
為了在 Postgres 中獲取索引資訊，我使用了以下查詢，但結果證明這與 Postgres < 9.4 不兼容，因為使用了WITH ORDINALITY.
我真的很難以適用于早期版本的方式重寫它。希望有人可以提供一些建議？
SELECT i.indexrelid::regclass AS indexname,
k.i AS index_order,
i.indexrelid as id,
i.indisunique,
i.indisprimary,
coalesce(a.attname,
(('{' || pg_get_expr(
i.indexprs,
i.indrelid
)
|| '}')::text[]
)[k.i]
) AS index_column,
i.indoption[k.i - 1] = 0 AS ascending
FROM pg_index i
CROSS JOIN LATERAL unnest(i.indkey) WITH ORDINALITY AS k(attnum, i)
LEFT JOIN pg_attribute AS a
ON i.indrelid = a.attrelid AND k.attnum = a.attnum
JOIN pg_class t on t.oid = i.indrelid
JOIN pg_namespace c on c.oid = t.relnamespace
WHERE
c.nspname = $1 AND
t.relname = $2
uj5u.com熱心網友回復：
你可以試試這個已經在 postgres 9.5 中測驗過的，但之前沒有：
SELECT i.indexrelid::regclass AS indexname,
k.i AS index_order,
i.indexrelid as id,
i.indisunique,
i.indisprimary,
coalesce(a.attname,
(('{' || pg_get_expr(
i.indexprs,
i.indrelid
)
|| '}')::text[]
)[k.i]
) AS index_column,
i.indoption[k.i - 1] = 0 AS ascending
FROM pg_index i
CROSS JOIN LATERAL (SELECT unnest(i.indkey), generate_subscripts(i.indkey, 1) 1) AS k(attnum, i)
LEFT JOIN pg_attribute AS a
ON i.indrelid = a.attrelid AND k.attnum = a.attnum
JOIN pg_class t on t.oid = i.indrelid
JOIN pg_namespace c on c.oid = t.relnamespace
WHERE
c.nspname = $1 AND
t.relname = $2
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/384185.html
標籤：PostgreSQL的
上一篇：關于SQL陳述句的執行順序