1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
|
#!/usr/bin/perl -w
#
# PostGIS - Spatial Types for PostgreSQL
# http://postgis.net
#
# Copyright (C) 2016 Regina Obe <lr@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;
my $search_path_safe = -1; # we can put a search path on it without disrupting spatial index use
if ($2 eq ')') ## reached end of header
{
$endhead = 1;
}
if ( /((add|drop)[\_]*(geometry|overview|raster)|internal)/i){
# can't put search_path on addgeometrycolumn or addrasterconstraints
# since table names are sometimes passed in non-qualified
# also can't work on some functions that take internals
$search_path_safe = 0;
}
if ( /st_transform/i){
# st_transform functions query spatial_ref_sys
# so could fail in materialized views and spatial indexes,
# though often all done in C
$search_path_safe = 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;
#check to see if function is STRICT or c or plpgsql
# we can't put search path on non-STRICT sql since search path breaks SQL inlining
# breaking sql inlining will break use of spatial index
my $endfunc = 0;
while(<INPUT>)
{
$endfunc = 1 if /^\s*(\$\$\s*)?LANGUAGE /i;
if ( $endfunc == 1 && $search_path_safe == -1 ){
$search_path_safe = 1 if /LANGUAGE\s+[\']*(plpgsql)/i;
$search_path_safe = 1 if /STRICT/i;
#exclude C functions unless we've include,
# in most cases except ST_Transform
# c functions don't call dependent functions or tables
$search_path_safe = 0 if /LANGUAGE\s+[\']*(c)/i;
}
last if ( $endfunc && /\;/ );
}
if ($search_path_safe == 1)
{
print "EXECUTE 'ALTER FUNCTION $funchead $endfunchead SET search_path=' || quote_ident(param_postgis_schema) || ',pg_catalog;';\n";
}
}
}
close( INPUT );
## End of DO
print 'END;';
print '$$;';
__END__
|
