[postgis-tickets] r14749 - script to set search_path of functions.

[Regina Obe]

Author: robe
Date: 2016-03-04 17:53:49 -0800 (Fri, 04 Mar 2016)
New Revision: 14749

Added:
   trunk/utils/postgis_proc_set_search_path.pl
Log:
script to set search_path of functions.  
Sadly this thwarts use of spatial indexes.  
Need to think about this more. References #3490

Added: trunk/utils/postgis_proc_set_search_path.pl
===================================================================
--- trunk/utils/postgis_proc_set_search_path.pl	                        (rev 0)
+++ trunk/utils/postgis_proc_set_search_path.pl	2016-03-05 01:53:49 UTC (rev 14749)
@@ -0,0 +1,145 @@
+#!/usr/bin/perl -w
+
+#
+# PostGIS - Spatial Types for PostgreSQL
+# http://postgis.net
+#
+# Copyright (C) 2016 Regina Obe <lr at pcorp.us>
+#
+# This is free software; you can redistribute and/or modify it under
+# the terms of the GNU General Public Licence. See the COPYING file.
+#
+
+#
+# This script produces an .sql file containing
+# ALTER FUNCTION SET PATH calls for each function
+# in postgis.sql
+
+# Having postgis functions force the search path 
+# to just where postgis is installed is needed
+# so that functions that call other functions during 
+# database restore, materialized view creation, foreign table calls
+# will always be able to find the companion functions
+#
+
+eval "exec perl -w $0 $@"
+	if (0);
+
+use strict;
+use warnings;
+
+#
+# Commandline argument handling
+#
+($#ARGV == 0) ||
+die "Usage: perl postgis_proc_set_path.pl <postgis.sql> <version_from> [<schema>]\nCreates a new SQL script 
+to set search path for all functions in input script file.\n"
+	if ( @ARGV < 1 || @ARGV > 3 );
+
+my $sql_file = $ARGV[0];
+my $module = 'postgis';
+my $soname = '';
+my $version_to = "";
+my $version_to_num = 0;
+my $version_from = $ARGV[1];
+my $version_from_num = 0;
+my $schema = "";
+$schema = $ARGV[2] if @ARGV > 2;
+
+die "Unable to open input SQL file $sql_file\n"
+	if ( ! -f $sql_file );
+
+## Header of do	
+print 'DO language plpgsql $$';
+my $dofunc_start = <<"EOF";
+DECLARE param_postgis_schema text;
+BEGIN
+-- check if PostGIS is already installed
+param_postgis_schema = (SELECT n.nspname from pg_extension e join pg_namespace n on e.extnamespace = n.oid WHERE extname = 'postgis');
+
+-- if in middle install, it will be the current_schema or what was there already
+param_postgis_schema = COALESCE(param_postgis_schema, current_schema());
+
+IF param_postgis_schema != current_schema() THEN
+	EXECUTE 'set search_path TO ' || quote_ident(param_postgis_schema);
+END IF;
+
+-- PostGIS set search path of functions
+EOF
+print $dofunc_start;
+
+#
+# Search the SQL file for the target version number (the 
+# version we are upgrading *to*.
+#
+open( INPUT, $sql_file ) || die "Couldn't open file: $sql_file\n";
+
+
+print qq{
+--
+-- ALTER FUNCTION script
+--
+
+};
+
+#print "BEGIN;\n";
+print "SET search_path TO $schema;\n" if $schema;
+
+#
+# Go through the SQL file and find all functions
+# for each create an ALTER FUNCTION statement
+# to set the search_path to schema postgis is installed in
+open( INPUT, $sql_file ) || die "Couldn't open file: $sql_file\n";
+while(<INPUT>)
+{
+
+	if ( /^create or replace function([^\)]+)([\)]{0,1})/i )
+	{
+		my $funchead = $1; #contains function header except the end )
+		my $endhead = 0;
+		my $endfunchead = $2;
+		if ($2 eq ')') ## reached end of header
+		{
+			$endhead = 1;
+		}
+		
+		#raster folks decided to break their func head in multiple lines 
+		# so we need to do this crazy thing
+		if ($endhead != 1)
+		{
+			while(<INPUT>)
+			{
+				#look for expressions with no ( and optionally ending in )
+				if ( /^([^\)]*)([\)]{0,1})/i )
+				{
+					$funchead .= $1;
+					$endfunchead = $2;
+					if ($2 eq ')') ## reached end of header
+					{
+						$endhead = 1;
+						
+					}
+				}
+				last if ( $endhead );
+	
+			}
+		}
+		#strip quoted , trips up the default strip
+		$funchead =~ s/(',')+//ig;
+		#strip off default args from the function header
+		$funchead =~ s/(default\s+[A-Za-z\.\+\-0-9\'\[\]\:\s]*)//ig;
+		
+
+		print "EXECUTE 'ALTER FUNCTION $funchead $endfunchead SET search_path=' || quote_ident(param_postgis_schema) || ';';\n";
+	}
+
+}
+
+close( INPUT );
+
+## End of DO
+print 'END;';
+print '$$';
+
+__END__
+