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;