Retrieving foreign key information via system tables

[jisoo23]

Hello all,

This is a rather difficult question but I'm sure someone's probably figured this out, has anyone been able to pull foreign key information from Postgresql's system tables? I found a query that does most of what I want it to do, it retrieves the foreign key name, and the columns that are linked. The only problem is that it does not give me the name of the foreign table (I already have the base table name). Here is the query:

SELECT pt.tgargs FROM pg_class pc, pg_proc pg_proc, pg_proc pg_proc_1, pg_trigger pg_trigger, pg_trigger pg_trigger_1, pg_proc pp, pg_trigger pt WHERE pt.tgrelid = pc.oid AND pp.oid = pt.tgfoid AND pg_trigger.tgconstrrelid = pc.oid AND pg_proc.oid = pg_trigger.tgfoid AND pg_trigger_1.tgfoid = pg_proc_1.oid AND pg_trigger_1.tgconstrrelid = pc.oid AND ((pc.relname= '<< TABLENAME >>>') AND (pp.proname LIKE '%%ins') AND (pg_proc.proname LIKE '%%upd') AND (pg_proc_1.proname LIKE '%%del') AND (pg_trigger.tgrelid=pt.tgconstrrelid) AND (pg_trigger_1.tgrelid = pt.tgconstrrelid));

Any suggestions or help is greatly appreciated.

Thanks!
Jisoo23

 

[ceco]

it is a little different for the different versions

when there is a problem like that, I like to look how phpPgAdmin has solved the problem, and here is the solution for version 7.4

(it gives, a little more information - all the constraints, not only foreign keys, but you can easy change it)

SELECT
pc.conname,
pg_catalog.pg_get_constraintdef(pc.oid, true) AS consrc,
pc.contype,
CASE WHEN pc.contype='u' OR pc.contype='p' THEN (
SELECT
indisclustered
FROM
pg_catalog.pg_depend pd,
pg_catalog.pg_class pl,
pg_index pi
WHERE
pd.refclassid=pc.tableoid
AND pd.refobjid=pc.oid
AND pd.objid=pl.oid
AND pl.oid=pi.indexrelid
) ELSE
NULL
END AS indisclustered
FROM
pg_catalog.pg_constraint pc
WHERE
pc.conrelid = (SELECT oid FROM pg_class WHERE relname='vic_vic_category'
AND relnamespace = (SELECT oid FROM pg_namespace
WHERE nspname='fun'))
ORDER BY
1

 

[jisoo23]

Thanks for the reply! One question, the "WHERE" clause in the query, I understand the relname portion but what about nspname at the bottom? I'm not sure what that field stands for.

Thanks,
Jisoo23

 

[jisoo23]

A-ha, I think I figured it out. I just need to change 'fun' to 'public'. Nevermind! =)

 

[ceco]

yes ;-) forgot to mention that these are my schema and table names ;-)