From 916c351c9481e05a8e3aca6ed56b6514350ef37e Mon Sep 17 00:00:00 2001
From: Sandro Santilli <strk@keybit.net>
Date: Wed, 28 Mar 2012 14:02:31 +0000
Subject: [PATCH] Strip schemas from search_path as part of uninstall
 procedures

git-svn-id: http://svn.osgeo.org/postgis/trunk@9570 b70326c6-7e19-0410-871a-916f4a2858ee
---
 utils/create_undef.pl | 46 +++++++++++++++++++++++++++++++++++++++++--
 1 file changed, 44 insertions(+), 2 deletions(-)

diff --git a/utils/create_undef.pl b/utils/create_undef.pl
index 21c189b24..80232dd62 100755
--- a/utils/create_undef.pl
+++ b/utils/create_undef.pl
@@ -269,9 +269,15 @@ foreach my $fn (@type_funcs)
 }
 
 print "-- Drop all schemas.\n";
-foreach my $schema (@schemas)
+if (@schemas)
 {
-	print "DROP SCHEMA $schema;\n";
+  print <DATA>;
+  foreach my $schema (@schemas)
+  {
+    print "SELECT undef_helper.StripFromSearchPath('$schema');\n";
+    print "DROP SCHEMA \"$schema\";\n";
+  }
+  print "DROP SCHEMA undef_helper CASCADE;\n";
 }
 
 
@@ -281,3 +287,39 @@ print "COMMIT;\n";
 
 1;
 
+__END__
+create schema undef_helper;
+--{
+--  StripFromSearchPath(schema_name)
+--
+-- Strips the specified schema from the database search path
+-- 
+-- This is a helper function for uninstall
+-- We may want to move this function as a generic helper
+--
+CREATE OR REPLACE FUNCTION undef_helper.StripFromSearchPath(a_schema_name varchar)
+RETURNS text
+AS
+$$
+DECLARE
+	var_result text;
+	var_search_path text;
+BEGIN
+	SELECT reset_val INTO var_search_path FROM pg_settings WHERE name = 'search_path';
+	IF var_search_path NOT LIKE '%' || quote_ident(a_schema_name) || '%' THEN
+		var_result := a_schema_name || ' not in database search_path';
+	ELSE
+    var_search_path := btrim( regexp_replace(
+        replace(var_search_path, a_schema_name, ''), ', *,', ','),
+        ', ');
+    RAISE NOTICE 'New search_path: %', var_search_path;
+		EXECUTE 'ALTER DATABASE ' || quote_ident(current_database()) || ' SET search_path = ' || var_search_path;
+		var_result := a_schema_name || ' has been stripped off database search_path ';
+	END IF;
+  
+  RETURN var_result;
+END
+$$
+LANGUAGE 'plpgsql' VOLATILE STRICT;
+
+--} StripFromSearchPath
-- 
2.40.0