Stored procedure for adding cascade

As simple answer to the question, yes, it is possible to do this with a function.

PostgreSQL has several very powerful function languages that you can use for this purpose. Being powerful, you can do some pretty neat things with them. You can also shoot yourself in the foot very easily.

The solution is very brute-force and inelegant, and I wouldn't recommend using it on a production database at all.

The function that answers this doesn't give you any output in the logs, and it's basically an all-or-nothing proposition in your database, so running it in a transaction is essential.

I would highly recommend figuring out exactly what it's doing and why it is doing it before running it.

It would be better to explicitly create an sql script to add ON DELETE CASCADE for each key that you want to update. This would be the most flexible, introspect-able, and modify-able solution to your problem.

The code below worked on the pgbench tables and their foreign keys for me in a test database I created. I don't guarantee it will work in other cases.

It looks something like this:

BEGIN;

DO $$DECLARE r record;
   BEGIN
      FOR r IN SELECT c.relname, conname, cr.oid AS croid FROM pg_catalog.pg_constraint cr, pg_catalog.pg_class c WHERE c.oid = cr.conrelid AND cr.contype = 'f' ORDER BY 1
   LOOP 
      EXECUTE 'ALTER TABLE '||quote_ident(r.relname)||' DROP CONSTRAINT '||quote_ident(r.conname)||',ADD CONSTRAINT '||quote_ident(r.conname)||' '||pg_catalog.pg_get_constraintdef(r.croid, true)||' '||' ON DELETE CASCADE';
   END LOOP;
END$$;

It dynamically creates

ALTER TABLE foo DROP CONSTRAINT bar_fk, ADD CONSTRAINT bar_fk FOREIGN KEY (baz) REFERENCES bar(baz) ON DELETE CASCADE;

and executes it in a loop in the anonymous function for every referenced constraint in pg_catalog.pg_constraint of type f (foreign key), joined to the pg_catalog.pg_class table to get the relation name. If you needed it to be smarter than that, you'd want to alter the statement in the loop accordingly to fit your needs.

Once you've checked the results for your tables with d or some other method, and are satisfied with the results, then you execute the COMMIT at the end.