PostgreSQL: List of views/table rules that depend on a given table

Question

How can I find all Views and Tables with rules which depend on a given Table?

I need this in order to find which views and tables I have to check if I want to alter/drop that given Table.

Answer 1

Get Views or Tables which refer to a given table TABLENAME:

SELECT cl_r.relname AS ref_table
FROM pg_rewrite AS r
JOIN pg_class AS cl_r ON r.ev_class=cl_r.oid
JOIN pg_depend AS d ON r.oid=d.objid
JOIN pg_class AS cl_d ON d.refobjid=cl_d.oid
WHERE cl_d.relkind IN ('r','v') AND cl_d.relname='TABLENAME'
GROUP BY cl_r.relname
ORDER BY cl_r.relname;

I did it by reversing the following answer: https://stackoverflow.com/a/4337615

Answer 2

If I understand correctly, you want the rules not the relations. If you want the custom defined ones you can:

SELECT * from pg_rules WHERE tablename = 'TABLENAME'

if you want to see the system defines ones (for examples the one from the views) you can:

SELECT
    N.nspname AS schemaname,
    C.relname AS tablename,
    R.rulename AS rulename,
    pg_get_ruledef(R.oid) AS definition
FROM (pg_rewrite R JOIN pg_class C ON (C.oid = R.ev_class))
    LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE C.relname = 'TABLENAME';

I just took the query from the definition of pg_rules that is actually a system view (with \d+ pg_rules), and changed the WHERE clause, that is normally:

WHERE r.rulename <> '_RETURN'::name;