I have a database with an old broken version of PostGIS installed in it. I would like to easily drop all functions in the database (they're all from PostGIS). Is there a simple way to do this? Even simply extracting a list of function names would be acceptable as I could just make a large DROP FUNCTION statement.
Add a comment
|
2 Answers
A fine answer to this question can be found here:
SELECT 'DROP FUNCTION ' || ns.nspname || '.' || proname
|| '(' || oidvectortypes(proargtypes) || ');'
FROM pg_proc INNER JOIN pg_namespace ns ON (pg_proc.pronamespace = ns.oid)
WHERE ns.nspname = 'my_messed_up_schema' order by proname;
3 Comments
Drux
Oftentimes,
ms.nspname = 'public' applies.
oᴉɹǝɥɔ
The above SQL will fail on not lower case or not alphanumeric function/schema names. Better use quote_ident or format('DROP FUNCTION %I.%I(%s);', nspname, proname, oidvectortypes(proargtypes)) to get SQL identifiers quoted correctly.
mpowered
Also worth mentioning, this doesn't actually drop the functions, just generates the drop commands to be run subsequently. Bit of a gotcha :-)
Just as there was a postgis.sql enabler install script, there is also an uninstall_postgis.sql uninstall script.
psql -d [yourdatabase] -f /path/to/uninstall_postgis.sql
Warning: Be prepared to see your geometry/geography columns and data disappear!
