Using plpgsql to remove all functions

Question

I intended to drop all functions in all schemas within a certain database, however, after I executed the bash file containing the following code block, the functions are still there

# use pg_dump and pg_restore to migrate some databases from another server

psql $DBNAME << EOF
DO \$$
DECLARE
    myschema RECORD;
    myview RECORD;
    myfunc RECORD;
    myseq RECORD;
BEGIN

    FOR myschema IN (SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE 'pg_%' AND schema_name <> 'information_schema')
    LOOP

        -- Drop all views
        FOR myview IN (SELECT viewname FROM pg_catalog.pg_views WHERE schemaname=myschema.schema_name)
        LOOP
            EXECUTE 'DROP VIEW ' || quote_ident(myschema.schema_name) || '.'  || quote_ident(myview.viewname) || ';';
        END LOOP;

        -- Drop all sequences
        FOR myseq IN (SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema=myschema.schema_name)
        LOOP
            EXECUTE 'DROP SEQUENCE ' || quote_ident(myschema.schema_name) || '.'  || quote_ident(myseq.sequence_name) || ';';
        END LOOP;

        -- Drop all functions
        FOR myfunc IN (SELECT routine_name FROM information_schema.routines WHERE routine_type='FUNCTION' AND specific_schema=myschema.schema_name )
        LOOP
            EXECUTE 'DROP FUNCTION ' || quote_ident(myschema.schema_name) || '.'  || quote_ident(myfunc.routine_name) || ';';
        END LOOP;

    END LOOP;

END;
\$$ LANGUAGE plpgsql;

---

After I partition the do block into three separate do blocks for dropping all views/functions/sequences, I managed to drop all sequences. I assume this is because the transaction must be atomic. However, because I intend to clean the databases and drop all unused functions/sequences/views, I cannot know ahead which are dependent ones and will raise errors. Can I ask plpgsql to ignore the error so that the unused views/functions/functions can be successfully dropped?

---

I revised my code to based the suggestions from @LaurenzAlbe. However, I got to error ERROR: syntax error at or near "EXCEPTION". Did I place the EXCEPTION statements in the wrong place?

psql $DBNAME << EOF    
DO \$$
DECLARE
    myschema RECORD;
    myseq RECORD;
    myview RECORD;
    myfunc RECORD;
BEGIN
    FOR myschema IN (SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE 'pg_%' AND schema_name <> 'information_schema')
    LOOP
        -- Drop all functions
        FOR myfunc IN (SELECT routine_name FROM information_schema.routines WHERE routine_type='FUNCTION' AND specific_schema=myschema.schema_name)
        LOOP
            EXECUTE format('DROP FUNCTION %I.%I', myschema.schema_name, myfunc.routine_name);

            EXCEPTION
            WHEN dependent_objects_still_exist THEN
            RAISE WARNING 'Could not drop object %.% because of dependencies', myschema.schema_name, myfunc.routine_name;
        END LOOP;

        -- Drop all sequences
        FOR myseq IN (SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema=myschema.schema_name)
        LOOP
            EXECUTE format('DROP SEQUENCE %I.%I', myschema.schema_name, myseq.sequence_name);

            EXCEPTION
            WHEN dependent_objects_still_exist THEN
            RAISE WARNING 'Could not drop object %.% because of dependencies', myschema.schema_name, myseq.sequence_name;
        END LOOP;

        -- Drop all views
        FOR myview IN (SELECT viewname FROM pg_catalog.pg_views WHERE schemaname=myschema.schema_name)
        LOOP
            EXECUTE format('DROP VIEW %I.%I', myschema.schema_name, myview.viewname);

            EXCEPTION
            WHEN dependent_objects_still_exist THEN
            RAISE WARNING 'Could not drop object %.% because of dependencies', myschema.schema_name, myview.viewname;
        END LOOP;
    END LOOP;

END;
\$$ LANGUAGE plpgsql;

Answer 1

Your code is executed in a single DO statement and hence in a single transaction. So if any of the statements cause an error, all statements will be rolled back.

If you want to avoid that, you can use subtransactions by using an EXCEPTION clause, for example:

BEGIN
    EXECUTE format('DROP SEQUENCE %I.%I',
                   myschema.schema_name),
                   myseq.sequence_name);
EXCEPTION
    WHEN dependent_objects_still_exist THEN
        RAISE WARNING 'could not drop object %.% because of dependencies',
                      myschema.schema_name,
                      myseq.sequence_name;
END;

Then the failed statement is rolled back, but processing continues.

Another thing you could consider is using

DROP ... CASCADE

which will remove dependent objects automatically.