find_related_tables.sql

-- Finds other tables referencing given one.
CREATE OR REPLACE FUNCTION find_related_tables(
    target_table_name TEXT,
    target_schema_name TEXT DEFAULT 'public'
)
RETURNS TABLE (
    referencing_table_schema TEXT,
    referencing_table TEXT,
    referencing_column TEXT,
    referenced_table_schema TEXT,
    referenced_table TEXT,
    referenced_column TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        tc.table_schema::TEXT AS referencing_table_schema,
        tc.table_name::TEXT AS referencing_table,
        kcu.column_name::TEXT AS referencing_column,
        ccu.table_schema::TEXT AS referenced_table_schema,
        ccu.table_name::TEXT AS referenced_table,
        ccu.column_name::TEXT AS referenced_column
    FROM 
        information_schema.table_constraints AS tc
    JOIN 
        information_schema.key_column_usage AS kcu
        ON tc.constraint_name = kcu.constraint_name
        AND tc.table_schema = kcu.table_schema
    JOIN 
        information_schema.constraint_column_usage AS ccu
        ON ccu.constraint_name = tc.constraint_name
        AND ccu.table_schema = tc.table_schema
    WHERE 
        tc.constraint_type = 'FOREIGN KEY'
        AND ccu.table_name = target_table_name
        AND ccu.table_schema = target_schema_name;
END;
$$;