联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在pg中查询一个表相关的所有oid(表OID、TOAST表OID、索引OID、TOAST表的索引OID、约束OID)
-- 替换这里的表名和模式名
\set search_path = public;
\set table_name '\'AO_6384AB_DISCOVERED\''
\set schema_name '\'public\''
WITH table_info AS (
-- 基础表信息：表OID、TOAST表OID
SELECT
c.oid AS table_oid,
c.relname AS table_name,
c.reltoastrelid AS toast_oid,
n.nspname AS schema_name
FROM
pg_catalog.pg_class c
JOIN
pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE
c.relname = :table_name
AND n.nspname = :schema_name
AND c.relkind = 'r'
),
index_info AS (
-- 索引信息：索引OID
SELECT
t.table_oid,
i.oid AS object_oid,
i.relname AS object_name,
'index' AS object_type
FROM
pg_catalog.pg_class i
JOIN
pg_catalog.pg_index idx ON i.oid = idx.indexrelid
JOIN
table_info t ON idx.indrelid = t.table_oid
WHERE
i.relkind = 'i'
),
toast_info AS (
-- TOAST表信息：TOAST表OID
SELECT
t.table_oid,
t.toast_oid AS object_oid,
c.relname AS object_name,
'toast_table' AS object_type
FROM
table_info t
JOIN
pg_catalog.pg_class c ON t.toast_oid = c.oid
WHERE
t.toast_oid <> 0
),
toast_index_info AS (
-- TOAST索引信息：TOAST表的索引OID
SELECT
t.table_oid,
i.oid AS object_oid,
i.relname AS object_name,
'toast_index' AS object_type
FROM
table_info t
JOIN
pg_catalog.pg_class c ON t.toast_oid = c.oid
JOIN
pg_catalog.pg_index idx ON c.oid = idx.indrelid
JOIN
pg_catalog.pg_class i ON idx.indexrelid = i.oid
WHERE
t.toast_oid <> 0
AND i.relkind = 'i'
),
constraint_info AS (
-- 约束信息：约束OID
SELECT
t.table_oid,
con.oid AS object_oid,
con.conname AS object_name,
CASE con.contype
WHEN 'p' THEN 'primary_key'
WHEN 'u' THEN 'unique_constraint'
WHEN 'f' THEN 'foreign_key'
WHEN 'c' THEN 'check_constraint'
ELSE 'other_constraint'
END AS object_type
FROM
pg_catalog.pg_constraint con
JOIN
table_info t ON con.conrelid = t.table_oid
)
-- 合并所有结果，先输出表本身的信息，再输出其他关联对象
SELECT
t.table_oid AS object_oid,
t.table_name AS object_name,
'table' AS object_type,
t.schema_name AS schema_name
FROM
table_info t
UNION ALL
SELECT
object_oid,
object_name,
object_type,
(SELECT schema_name FROM table_info) AS schema_name
FROM
index_info
UNION ALL
SELECT
object_oid,
object_name,
object_type,
(SELECT schema_name FROM table_info) AS schema_name
FROM
toast_info
UNION ALL
SELECT
object_oid,
object_name,
object_type,
(SELECT schema_name FROM table_info) AS schema_name
FROM
toast_index_info
UNION ALL
SELECT
object_oid,
object_name,
object_type,
(SELECT schema_name FROM table_info) AS schema_name
FROM
constraint_info
ORDER BY
object_type;
