Pg_get_constraintdef(c.oid) AS definition No need to parse pg_get_constraintdef(), just use columns of pg_constraint table to obtain other details ( the docs).īased on Erwin's answer: SELECT c.conname AS constraint_name,ĪRRAY_AGG(col.attname ORDER BY u.attposition) AS "self_columns",ĪRRAY_AGG(f_col.attname ORDER BY f_u.attposition) AS "foreign_columns", Will return a table of form: | FK_Table | FK_Column | PK_Table | PK_Column | ORDER BY pg_get_constraintdef(c.oid), conrelid::regclass::text, contype DESC JOIN pg_namespace n ON n.oid = c.connamespaceĪND pg_get_constraintdef(c.oid) LIKE 'FOREIGN KEY %' ,CASE WHEN pg_get_constraintdef(c.oid) LIKE 'FOREIGN KEY %' THEN substring(pg_get_constraintdef(c.oid), position('(' in substring(pg_get_constraintdef(c.oid), 14))+14, position(')' in substring(pg_get_constraintdef(c.oid), position('(' in substring(pg_get_constraintdef(c.oid), 14))+14))-1) END AS "PK_Column" ,CASE WHEN pg_get_constraintdef(c.oid) LIKE 'FOREIGN KEY %' THEN substring(pg_get_constraintdef(c.oid), position(' REFERENCES ' in pg_get_constraintdef(c.oid))+12, position('(' in substring(pg_get_constraintdef(c.oid), 14))-position(' REFERENCES ' in pg_get_constraintdef(c.oid))+1) END AS "PK_Table" ,CASE WHEN pg_get_constraintdef(c.oid) LIKE 'FOREIGN KEY %' THEN substring(pg_get_constraintdef(c.oid), 14, position(')' in pg_get_constraintdef(c.oid))-14) END AS "FK_Column" Based on Erwin solution: SELECT conrelid::regclass AS "FK_Table"
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |