postgres finding child tables for particular table

Question

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).

Answer 1

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') 

to

and (ccu.table_schema, ccu.table_name) in (('a', 'plugin'))