I'm trying to get a list of foreign key constraints in the following form:
pk_table | pk_col | fk_table | fk_col
pg_get_constraintdef (which would force me to resort to string manipulation).
Is there any reason why the following query doesn't work?
SELECT x.conname, x.contype, x.conrelid, x.confrelid, pk.tablename, fk.tablename FROM pg_catalog.pg_constraint x INNER JOIN pg_catalog.pg_tables pk ON x.conrelid!=0 AND x.conrelid=(pk.tablename :: regclass :: oid) INNER JOIN pg_catalog.pg_tables fk ON x.confrelid!=0 AND x.confrelid=(fk.tablename :: regclass :: oid) WHERE x.connamespace in (SELECT oid FROM pg_catalog.pg_namespace n WHERE n.nspname in ('b2b', 'public')) AND x.contype='f'
ERROR: relation "sql_implementation_info" does not exist
My hypothesis was that PG is getting confused due to the presence of multiple namespaces, so I tried restricting the result-set to my known schemas, i.e.
b2b, public, but this still doesn't work.