23

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.

Jim Mitchener
  • 8,835
  • 7
  • 40
  • 56

2 Answers2

36

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;
ChristopheD
  • 112,638
  • 29
  • 165
  • 179
  • 2
    Oftentimes, `ms.nspname = 'public'` applies. – Drux Aug 20 '16 at 04:06
  • 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. – oᴉɹǝɥɔ Oct 03 '17 at 16:10
  • 1
    Also worth mentioning, this doesn't actually drop the functions, just generates the drop commands to be run subsequently. Bit of a gotcha :-) – mpowered Feb 22 '19 at 19:00
3

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!

Mike T
  • 41,085
  • 18
  • 152
  • 203