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
|
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
----
-- PostGIS - Spatial Types for PostgreSQL
-- http://postgis.net
--
-- Copyright (C) 2011 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.
--
-- Author: Regina Obe <lr@pcorp.us>
--
-- This is a suite of SQL helper functions for use during a PostGIS extension install/upgrade
-- The functions get uninstalled after the extention install/upgrade process
---------------------------
-- postgis_extension_remove_objects: This function removes objects of a particular class from an extension
-- this is needed because there is no ALTER EXTENSION DROP FUNCTION/AGGREGATE command
-- and we can't CREATE OR REPALCe functions whose signatures have changed and we can drop them if they are part of an extention
-- So we use this to remove it from extension first before we drop
CREATE OR REPLACE FUNCTION postgis_extension_remove_objects(param_extension text, param_type text)
RETURNS boolean AS
$$
DECLARE
var_sql text := '';
var_r record;
var_result boolean := false;
var_class text := '';
var_is_aggregate boolean := false;
var_sql_list text := '';
var_pgsql_version integer := CASE WHEN split_part(s,'.',1)::integer > 9 THEN split_part(s,'.',1) || '0' ELSE split_part(s,'.', 1) || split_part(s,'.', 2) END AS v
FROM substring(version(), 'PostgreSQL ([0-9\.]+)') AS s;
BEGIN
var_class := CASE WHEN lower(param_type) = 'function' OR lower(param_type) = 'aggregate' THEN 'pg_proc' ELSE '' END;
var_is_aggregate := CASE WHEN lower(param_type) = 'aggregate' THEN true ELSE false END;
IF var_pgsql_version < 110 THEN
var_sql_list := $sql$SELECT 'ALTER EXTENSION ' || e.extname || ' DROP ' || $3 || ' ' || COALESCE(proc.proname || '(' || oidvectortypes(proc.proargtypes) || ')' ,typ.typname, cd.relname, op.oprname,
cs.typname || ' AS ' || ct.typname || ') ', opcname, opfname) || ';' AS remove_command
FROM pg_depend As d INNER JOIN pg_extension As e
ON d.refobjid = e.oid INNER JOIN pg_class As c ON
c.oid = d.classid
LEFT JOIN pg_proc AS proc ON proc.oid = d.objid
LEFT JOIN pg_type AS typ ON typ.oid = d.objid
LEFT JOIN pg_class As cd ON cd.oid = d.objid
LEFT JOIN pg_operator As op ON op.oid = d.objid
LEFT JOIN pg_cast AS ca ON ca.oid = d.objid
LEFT JOIN pg_type AS cs ON ca.castsource = cs.oid
LEFT JOIN pg_type AS ct ON ca.casttarget = ct.oid
LEFT JOIN pg_opclass As oc ON oc.oid = d.objid
LEFT JOIN pg_opfamily As ofa ON ofa.oid = d.objid
WHERE d.deptype = 'e' and e.extname = $1 and c.relname = $2 AND COALESCE(proc.proisagg, false) = $4;$sql$;
ELSE -- for PostgreSQL 11 and above, they removed proc.proisagg among others and replaced with some func type thing
var_sql_list := $sql$SELECT 'ALTER EXTENSION ' || e.extname || ' DROP ' || $3 || ' ' || COALESCE(proc.proname || '(' || oidvectortypes(proc.proargtypes) || ')' ,typ.typname, cd.relname, op.oprname,
cs.typname || ' AS ' || ct.typname || ') ', opcname, opfname) || ';' AS remove_command
FROM pg_depend As d INNER JOIN pg_extension As e
ON d.refobjid = e.oid INNER JOIN pg_class As c ON
c.oid = d.classid
LEFT JOIN pg_proc AS proc ON proc.oid = d.objid
LEFT JOIN pg_type AS typ ON typ.oid = d.objid
LEFT JOIN pg_class As cd ON cd.oid = d.objid
LEFT JOIN pg_operator As op ON op.oid = d.objid
LEFT JOIN pg_cast AS ca ON ca.oid = d.objid
LEFT JOIN pg_type AS cs ON ca.castsource = cs.oid
LEFT JOIN pg_type AS ct ON ca.casttarget = ct.oid
LEFT JOIN pg_opclass As oc ON oc.oid = d.objid
LEFT JOIN pg_opfamily As ofa ON ofa.oid = d.objid
WHERE d.deptype = 'e' and e.extname = $1 and c.relname = $2 AND (proc.prokind = 'a') = $4;$sql$;
END IF;
FOR var_r IN EXECUTE var_sql_list USING param_extension, var_class, param_type, var_is_aggregate
LOOP
var_sql := var_sql || var_r.remove_command || ';';
END LOOP;
IF var_sql > '' THEN
EXECUTE var_sql;
var_result := true;
END IF;
RETURN var_result;
END;
$$
LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION postgis_extension_drop_if_exists(param_extension text, param_statement text)
RETURNS boolean AS
$$
DECLARE
var_sql_ext text := 'ALTER EXTENSION ' || quote_ident(param_extension) || ' ' || replace(param_statement, 'IF EXISTS', '');
var_result boolean := false;
BEGIN
BEGIN
EXECUTE var_sql_ext;
var_result := true;
EXCEPTION
WHEN OTHERS THEN
--this is to allow ignoring if the object does not exist in extension
var_result := false;
END;
RETURN var_result;
END;
$$
LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION postgis_extension_AddToSearchPath(a_schema_name varchar)
RETURNS text
AS
$$
DECLARE
var_result text;
var_cur_search_path text;
BEGIN
SELECT reset_val INTO var_cur_search_path FROM pg_settings WHERE name = 'search_path';
IF var_cur_search_path LIKE '%' || quote_ident(a_schema_name) || '%' THEN
var_result := a_schema_name || ' already in database search_path';
ELSE
var_cur_search_path := var_cur_search_path || ', '
|| quote_ident(a_schema_name);
EXECUTE 'ALTER DATABASE ' || quote_ident(current_database())
|| ' SET search_path = ' || var_cur_search_path;
var_result := a_schema_name || ' has been added to end of database search_path ';
END IF;
EXECUTE 'SET search_path = ' || var_cur_search_path;
RETURN var_result;
END
$$
LANGUAGE 'plpgsql' VOLATILE STRICT;
|
