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
|
-- ==============================================================
-- GNUmed database schema change script
--
-- License: GPL v2 or later
-- Author: karsten.hilbert@gmx.net
--
-- ==============================================================
\set ON_ERROR_STOP 1
set default_transaction_read_only to off;
set check_function_bodies to on;
-- --------------------------------------------------------------
create or replace function gm.add_missing_array_bits()
returns boolean
language 'plpgsql'
as '
DECLARE
BEGIN
-- array_agg
perform 1 from pg_catalog.pg_aggregate where aggfnoid::oid = (select oid from pg_catalog.pg_proc where proname = ''array_agg''::name limit 1);
if FOUND then
raise NOTICE ''[gm.add_missing_array_bits]: aggregate <array_agg> already exists'';
else
raise NOTICE ''[gm.add_missing_array_bits]: aggregate <array_agg> does not exist (probably PostgreSQL <8.4), creating'';
CREATE AGGREGATE array_agg(anyelement) (
SFUNC = array_append,
STYPE = anyarray,
INITCOND = ''{}''
);
comment on aggregate array_agg(anyelement) is
''Missing on PG 8.3, needed for vaccination handling starting with conversion from gnumed_v13 to gnumed_v14.'';
end if;
-- unnest()
perform 1 from pg_catalog.pg_proc where
proname = ''array_unnest''::name
and
pronamespace = (select oid from pg_namespace where nspname = ''gm''::name)
;
if FOUND then
raise NOTICE ''[gm.add_missing_array_bits]: function "gm.array_unnest()" already exists'';
else
raise NOTICE ''[gm.add_missing_array_bits]: function "gm.array_unnest()" does not exist, creating'';
CREATE OR REPLACE FUNCTION gm.array_unnest(anyarray)
RETURNS SETOF anyelement
AS ''
SELECT $1[i] FROM
generate_series (
array_lower($1,1),
array_upper($1,1)
) i
;''
LANGUAGE ''sql''
IMMUTABLE
;
comment on function gm.array_unnest(anyarray) is
''Missing on PG 8.3, needed for vaccination handling starting with conversion from gnumed_v13 to gnumed_v14.'';
end if;
perform 1 from pg_catalog.pg_proc where
proname = ''unnest''::name
and
pronamespace = (select oid from pg_namespace where nspname = ''pg_catalog''::name)
;
if FOUND then
raise NOTICE ''[gm.add_missing_array_bits]: function "pg_catalog.unnest()" exists'';
-- also exists in public ?
perform 1 from pg_catalog.pg_proc where
proname = ''unnest''::name
and
pronamespace = (select oid from pg_namespace where nspname = ''public''::name)
;
if FOUND then
raise NOTICE ''[gm.add_missing_array_bits]: function "unnest()" also exists in schema "public", removing'';
drop function public.unnest(anyarray) cascade;
end if;
else
raise NOTICE ''[gm.add_missing_array_bits]: function "pg_catalog.unnest()" does not exist (probably PostgreSQL <8.4)'';
-- exists in public ?
perform 1 from pg_catalog.pg_proc where
proname = ''unnest''::name
and
pronamespace = (select oid from pg_namespace where nspname = ''public''::name)
;
if FOUND then
raise NOTICE ''[gm.add_missing_array_bits]: function "public.unnest()" already exists'';
else
raise NOTICE ''[gm.add_missing_array_bits]: function "public.unnest()" does not exist either, creating'';
CREATE OR REPLACE FUNCTION public.unnest(anyarray)
RETURNS SETOF anyelement
AS ''SELECT gm.array_unnest($1);''
LANGUAGE ''sql''
IMMUTABLE
;
comment on function public.unnest(anyarray) is
''Missing on PG 8.3, needed for vaccination handling starting with conversion from gnumed_v13 to gnumed_v14.'';
end if;
end if;
return TRUE;
END;';
comment on function gm.add_missing_array_bits() is
'Add array aggregate and array unnesting to PostgreSQL versions lacking this functionality (IOW < 8.4).';
SELECT gm.add_missing_array_bits();
-- --------------------------------------------------------------
select gm.log_script_insertion('v14-add_missing_array_bits.sql', 'Revision: 1.1');
|
