I want to find all child tables corresponding to particular parent table.
SELECT * FROM information_schema.table_constraints tc right JOIN information_schema.constraint_column_usage ccu ON tc.constraint_catalog=ccu.constraint_catalog AND tc.constraint_schema = ccu.constraint_schema AND tc.constraint_name = ccu.constraint_name and ccu.table_name in ('plugin') WHERE lower(tc.constraint_type) in ('foreign key');
Now this is working fine in public schema. but when I use
'a.plugin' instead of
'plugin' where a is my schema then it is not working. (i.e it is not working inside particular schema when I try to use table inside schema as a parent table).
The schema name of a table is not stored in the column
table_name, it’s stored in the column
table_schema, so you need to change the condition:
and ccu.table_name in ('plugin')
and (ccu.table_schema, ccu.table_name) in (('a', 'plugin'))