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
|
-- ==============================================================
-- GNUmed database schema change script
--
-- Source database version: v2
-- Target database version: v3
--
-- What it does:
-- - add set_option functions
--
-- License: GPL v2 or later
-- Author: Karsten Hilbert
--
-- ==============================================================
-- $Id: cfg-set_option.sql,v 1.2 2007-09-24 23:31:17 ncq Exp $
-- $Revision: 1.2 $
-- --------------------------------------------------------------
\set ON_ERROR_STOP 1
-- --------------------------------------------------------------
create or replace function cfg.create_cfg_item(text, text, text, text, text)
returns integer
language 'plpgsql'
as '
declare
_option alias for $1;
_val_type alias for $2;
_workplace alias for $3;
_cookie alias for $4;
_owner alias for $5;
real_owner text;
pk_template integer;
pk_item integer;
begin
-- check template
select into pk_template pk from cfg.cfg_template where name = _option and type = _val_type;
if not FOUND then
insert into cfg.cfg_template (name, type) values (_option, _val_type);
select into pk_template currval(''cfg.cfg_template_pk_seq'');
end if;
if _owner is null then
select into real_owner CURRENT_USER;
else
real_owner := _owner;
end if;
-- check item
if _cookie is NULL then
select into pk_item pk from cfg.cfg_item where
fk_template = pk_template and
owner = real_owner and
workplace = _workplace and
cookie is null;
else
select into pk_item pk from cfg.cfg_item where
fk_template = pk_template and
owner = real_owner and
workplace = _workplace and
cookie = _cookie;
end if;
if FOUND then
return pk_item;
end if;
insert into cfg.cfg_item (
fk_template, workplace, cookie, owner
) values (
pk_template,
_workplace,
_cookie,
real_owner
);
select into pk_item currval(''cfg.cfg_item_pk_seq'');
return pk_item;
end;';
-- --------------------------------------------------------------
create or replace function cfg.set_option(text, anyelement, text, text, text)
returns boolean
language 'plpgsql'
as '
declare
_option alias for $1;
_value alias for $2;
_workplace alias for $3;
_cookie alias for $4;
_owner alias for $5;
val_type text;
pk_item integer;
rows integer;
cmd text;
begin
-- determine data type
if _value is of (text, char, varchar, name) then
val_type := ''string'';
elsif _value is of (smallint, integer, bigint, numeric, boolean) then
val_type := ''numeric'';
elsif _value is of (bytea) then
val_type := ''data'';
elsif _value is of (text[]) then
val_type := ''str_array'';
else
raise exception ''cfg.set_option(text, any, text, text, text): invalid type of value'';
end if;
-- create template/item if need be
select into pk_item cfg.create_cfg_item(_option, val_type, _workplace, _cookie, _owner);
-- set item value
cmd := ''select 1 from cfg.cfg_'' || val_type || '' where fk_item='' || pk_item || '';'';
execute cmd;
get diagnostics rows = row_count;
found := rows <> 0;
if FOUND then
if val_type = ''str_array'' then
cmd := ''update cfg.cfg_str_array set value=''''{"'' || array_to_string(_value, ''","'') || ''"}'''' where fk_item='' || pk_item || '';'';
elsif val_type = ''data'' then
cmd := ''update cfg.cfg_data set value='''''' || encode(_value, ''escape'') || '''''' where fk_item='' || pk_item || '';'';
else
cmd := ''update cfg.cfg_'' || val_type || '' set value='' || quote_literal(_value) || '' where fk_item='' || pk_item || '';'';
end if;
execute cmd;
return True;
end if;
if val_type = ''str_array'' then
cmd := ''insert into cfg.cfg_str_array(fk_item, value) values ('' || pk_item || '', ''''{"'' || array_to_string(_value, ''","'') || ''"}'''');'';
elsif val_type = ''data'' then
cmd := ''insert into cfg.cfg_data(fk_item, value) values ('' || pk_item || '', '''''' || encode(_value, ''escape'') || '''''');'';
else
cmd := ''insert into cfg.cfg_'' || val_type || '' (fk_item, value) values ('' || pk_item || '', '' || quote_literal(_value) || '');'';
end if;
execute cmd;
return True;
end;';
comment on function cfg.set_option(text, anyelement, text, text, text) is
'set option, owner = NULL means CURRENT_USER';
-- --------------------------------------------------------------
select public.log_script_insertion('$RCSfile: cfg-set_option.sql,v $', '$Revision: 1.2 $');
-- ==============================================================
-- $Log: cfg-set_option.sql,v $
-- Revision 1.2 2007-09-24 23:31:17 ncq
-- - remove begin; commit; as it breaks the bootstrapper
--
-- Revision 1.1 2006/09/25 10:55:01 ncq
-- - added here
--
-- Revision 1.3 2006/09/21 19:54:47 ncq
-- - we don't need set_option2()
--
-- Revision 1.2 2006/09/21 19:51:43 ncq
-- - eventually make set_option() work
--
-- Revision 1.1 2006/09/19 18:27:47 ncq
-- - add cfg.set_option()
-- - drop NOT NULL on cfg.cfg_item.cookie
--
-- Revision 1.3 2006/09/18 17:32:53 ncq
-- - make more fool-proof
--
-- Revision 1.2 2006/09/16 21:47:37 ncq
-- - improvements
--
-- Revision 1.1 2006/09/16 14:02:36 ncq
-- - use this as a template for change scripts
--
--
|
