How to find all object which will be dropped by CASCADE?

When I DROP something like this

ALTER TABLE "order_bt" DROP COLUMN "billed_to"

the command will list dependencies of the billed_to column:

DETAIL:  view "order" depends on column billed_to of table order_bt

But, this view order also has associated triggers, which would also be dropped when adding CASCADE to the above DROP command.

Is there an option that will allow me to see the full list of what would be dropped with DROP ... CASCADE?

I created an example structure to check dependencies. You can try it on your database, of course objects ID will be different.

CREATE TABLE parents (
  id_parent integer NOT NULL,
  name_parent varchar(100),
  CONSTRAINT pk_parents_id PRIMARY KEY (id_parent)
);

CREATE TABLE childs (
  id_child integer NOT NULL,
  name_child varchar(100),
  parent_id integer,
  CONSTRAINT pk_childs_id PRIMARY KEY (id_child),
  CONSTRAINT fk_childs_parent_id__parents FOREIGN KEY (parent_id)
      REFERENCES parents (id_parent) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE OR REPLACE VIEW public.parents_view AS 
 SELECT parents.id_parent,
    parents.name_parent
   FROM parents;

CREATE FUNCTION test_trigger() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' then
        raise notice 'INSERT trigger, NEW = [%]', NEW;
    ELSIF TG_OP = 'UPDATE' then
        raise notice 'UPDATE trigger, OLD = [%], NEW = [%]', OLD, NEW;
    ELSE
        raise notice 'DELETE trigger, OLD = [%]', OLD;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER parents_view_trigger INSTEAD OF INSERT OR UPDATE OR DELETE ON parents_view FOR EACH ROW EXECUTE PROCEDURE test_trigger();

Objects that depend of table "parents":

SELECT 
    refclassid, refobjid, refobjsubid,
    pg_describe_object(refclassid, refobjid, refobjsubid), 
    --pg_identify_object (refclassid, refobjid, refobjsubid) ,

    classid, objid, objsubid,
    pg_describe_object(classid, objid, objsubid), 
    --pg_identify_object (classid, objid, objsubid) ,
    pg_get_constraintdef(objid),

    deptype
    
FROM pg_depend 
WHERE refobjid IN ('parents'::regclass) -- and deptype IN ('n', 'a')
ORDER BY classid, refobjid;

Output:

Objects that depend of view "parents_view":

SELECT 
    refclassid, refobjid, refobjsubid,
    pg_describe_object(refclassid, refobjid, refobjsubid), 
    --pg_identify_object (refclassid, refobjid, refobjsubid),

    classid, objid, objsubid,
    pg_describe_object(classid, objid, objsubid), 
    --pg_identify_object (classid, objid, objsubid) ,
    pg_get_constraintdef(objid),

    deptype
    
FROM pg_depend 
WHERE refobjid IN ('parents_view'::regclass) -- and deptype IN ('n', 'a')
ORDER BY classid, refobjid;

Output:

You can check that view "parents_view" (object ID: 2305256) not depend of table parents but rule "rule _RETURN on view parents_view" (object ID: 2305259) does, and "trigger parents_view_trigger on view parents_view" depends on parents_view, this is the reason why recursive query do not return view dependent information.

Triggers are DEPENDENCY_AUTO type (a): The dependent object can be dropped separately from the referenced object, and should be automatically dropped (regardless of RESTRICT or CASCADE mode) if the referenced object is dropped. Example: a named constraint on a table is made auto-dependent on the table, so that it will go away if the table is dropped.

You can try a query like this, but you should try to improve it:

You can restrict the result filtering by deptype.

WITH RECURSIVE pg_depend_recursive AS (
                SELECT d.refclassid, d.refobjid, d.refobjsubid, d.classid, d.objid, d.objsubid, d.deptype
                FROM pg_depend d
                where refobjid = 'parents'::regclass

                UNION

                SELECT d.refclassid, d.refobjid, d.refobjsubid, d.classid, d.objid, d.objsubid, d.deptype
                FROM pg_depend d
                INNER JOIN pg_depend_recursive dr ON d.refobjid = dr.objid),

        pg_depend_recursive2 AS (SELECT d.refclassid, d.refobjid, d.refobjsubid, d.classid, d.objid, d.objsubid, d.deptype 
                    FROM pg_depend_recursive d

                    UNION

                    SELECT d.refclassid, d.refobjid, d.refobjsubid, d.classid, d.objid, d.objsubid, d.deptype
                    FROM pg_depend d
                    INNER JOIN pg_depend_recursive2 dr ON d.objid = dr.objid AND pg_describe_object(dr.classid, dr.objid, dr.objsubid) ilike 'rule%'),
                    
        pg_depend_recursive3 AS (SELECT d.refclassid, d.refobjid, d.refobjsubid, d.classid, d.objid, d.objsubid, d.deptype 
                    FROM pg_depend_recursive2 d

                    UNION

                    SELECT d.refclassid, d.refobjid, d.refobjsubid, d.classid, d.objid, d.objsubid, d.deptype
                    FROM pg_depend d
                    INNER JOIN pg_depend_recursive3 dr ON d.refobjid = dr.refobjid)
                    
SELECT refclassid, refobjid, refobjsubid, pg_describe_object(refclassid, refobjid, refobjsubid),
       classid, objid, objsubid, pg_describe_object(classid, objid, objsubid),
       deptype
FROM pg_depend_recursive3;