如果您不需要将其移植到另一个 RDBMS,那么使用目录表而不是标准信息模式会更快更简单pg_catalog
:
SELECT c.confrelid::regclass::text AS referenced_table
, c.conname AS fk_name
, pg_get_constraintdef(c.oid) AS fk_deFinition
FROM pg_attribute a
JOIN pg_constraint c ON (c.conrelid, c.conkey[1]) = (a.attrelid, a.attnum)
WHERE a.attrelid = '"Schema2"."TableB"'::regclass -- table name
AND a.attname = 'A_Id' -- column name
AND c.contype = 'f'
ORDER BY conrelid::regclass::text, contype DESC;
返回:
referenced_table | fk_name | fk_deFinition
------------------+-------------------------+----------------------------------------------
Schema1.TableA | b1_fkey | FOREIGN KEY ("B_id") REFERENCES "Schema1"."TableA"("A_id")