postgres_db_structure_functions.sql 19.2 KB
Newer Older
1

2
3
4
5
-- modifying the schema to allow an external_id

ALTER TABLE tce_user_groups ADD COLUMN external_id varchar(255);

6
7
8
9
10
11
-- modifying the schema to assign a study key to user in group

ALTER TABLE tce_usrgroups ADD COLUMN usrgrp_studykey character varying(255);

ALTER TABLE tce_tests_users ADD COLUMN testuser_studykey character varying(255);

12
13
14
15
16
17
18
-- modifying to log sent reminder emails

ALTER TABLE tce_user_groups ADD email1_sent character varying(255);
ALTER TABLE tce_user_groups ADD email2_sent character varying(255);
ALTER TABLE tce_user_groups ADD email3_sent character varying(255);
ALTER TABLE tce_user_groups ADD disabled character varying(255);

19
20
21
22
-- modifying the schema to assign group to test
ALTER TABLE tce_user_groups ADD COLUMN group_type character varying (5) DEFAULT 'T';
ALTER TABLE tce_tests ADD COLUMN test_group_id bigint;

23
24
25
-- restraint dropped because a user locked in VIS can be registered again in TCExam later, but of course keeps his  matrikelnr.
ALTER TABLE tce_users DROP CONSTRAINT ak_user_regnumber;

26
27
-- functions for administrative group
-- Function: get_admin_group(bigint, bigint)
28

29
30
-- DROP FUNCTION get_admin_group(bigint, bigint);

31
32
33
34
-- Function: get_admin_group(bigint, bigint)

-- DROP FUNCTION get_admin_group(bigint, bigint);

35
36
37
38
39
40
41
42
43
44
45
CREATE OR REPLACE FUNCTION get_admin_group(itest_id bigint, igroup_id bigint)
  RETURNS bigint AS
$BODY$
DECLARE xgroup_id bigint;
BEGIN
	SELECT group_id FROM tce_user_groups
		WHERE external_id = (
			SELECT LEFT(external_id,strpos(external_id,':')-1)
				FROM tce_user_groups
				WHERE group_id = igroup_id ) || ':owner'
		AND group_type = 'B'
46
47
48
49
		INTO xgroup_id;
		IF xgroup_id IS NULL THEN
			xgroup_id = igroup_id;
		END IF;
50
51
52
53
54
55
56
57
58
59
60
61
62
RETURN xgroup_id;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;
ALTER FUNCTION get_admin_group(bigint, bigint) SET search_path=tcexam, pg_temp;

ALTER FUNCTION get_admin_group(bigint, bigint)
  OWNER TO tcexam;

-- Function: set_admin_group(bigint, bigint)

-- DROP FUNCTION set_admin_group(bigint, bigint);
63
64
65

CREATE OR REPLACE FUNCTION set_admin_group(itest_id bigint, igroup_id bigint)
  RETURNS void AS
66
67
$BODY$
BEGIN
68
69
70
71
72
73
74
	IF NOT EXISTS (SELECT * FROM tce_testgroups WHERE tstgrp_group_id = (SELECT * FROM get_admin_group(itest_id, igroup_id)) AND tstgrp_test_id = itest_id)
	THEN
	INSERT INTO tce_testgroups (
		tstgrp_test_id,
		tstgrp_group_id
		) VALUES ( itest_id, (SELECT * FROM get_admin_group(itest_id, igroup_id)));
	END IF;
75
76
77
78
END;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;
79
ALTER FUNCTION set_admin_group(bigint, bigint) SET search_path=tcexam, pg_temp;
80

81
ALTER FUNCTION set_admin_group(bigint, bigint)
82
  OWNER TO tcexam;
83

84

85
-- functions for tcexam esb integration
86
87
88
89
90
91
92
93

-- assign_tcexam_user_to_group

CREATE OR REPLACE FUNCTION assign_tcexam_user_to_group(xmlbody text, pgroupid character varying, pappointmentid character varying)
  RETURNS void AS
$BODY$
    DECLARE
	login character varying;
94
95
96
97
98
	externalid character varying;
	userid bigint;
	groupid bigint;
	studykey character varying;
	uservo xml; 
99
        
100
101
102
    BEGIN

	uservo = xmlbody::xml;
103
104
	externalid = CONCAT (pgroupid,':',pappointmentid);
	SELECT (xpath('/userVO/login/text()', uservo))[1] into login;
105
	SELECT (xpath('/userVO/appointmentStudyKey/text()', uservo))[1] into studykey;
106

107
108
	SELECT user_id INTO userid FROM tce_users WHERE user_name = login;
	SELECT group_id INTO groupid FROM tce_user_groups WHERE external_id = externalid;
109

110
111
112
113
	IF NOT EXISTS(SELECT usrgrp_user_id FROM tce_usrgroups WHERE usrgrp_group_id = groupid AND usrgrp_user_id = userid) 
		THEN INSERT INTO tce_usrgroups (usrgrp_user_id, usrgrp_group_id, usrgrp_studykey) VALUES (userid, groupid, studykey);
	ELSE UPDATE tce_usrgroups SET usrgrp_studykey = studykey WHERE usrgrp_group_id = groupid and usrgrp_user_id = userid;
	END IF;
114
115
116
117

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
118
119
120
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
121
ALTER FUNCTION assign_tcexam_user_to_group(text, character varying, character varying)
122
  OWNER TO tcexam;
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


--  get_tcexam_group

CREATE OR REPLACE FUNCTION get_tcexam_group(externalid character varying)
  RETURNS text AS
$BODY$

DECLARE 
	xoutput text;

BEGIN
	IF NOT EXISTS(SELECT group_name from tce_user_groups where external_id like concat(externalid,':%'))
	THEN
		RETURN '<appointments><appointment><appointmentId>' || '' || '</appointmentId></appointment></appointments>';
	END IF;
	
SELECT XMLSERIALIZE ( CONTENT
	 
	 XMLELEMENT(name "appointments",
	 xmlagg(
		XMLELEMENT(name "appointment",
		XMLFOREST(
			split_part(u.external_id,':',2) as "appointmentId",
			u.group_id as "tcexamGroupKey",
			u.group_name as "tcexamGroupName"
			)
		)
	)
	)
		 
	as text )
		INTO xoutput
		FROM tce_user_groups u
		WHERE u.external_id LIKE concat(externalid,':%')
		;
	RETURN xoutput;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
163
164
165
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
166
ALTER FUNCTION get_tcexam_group(character varying)
167
  OWNER TO tcexam;
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183


-- get_tcexam_group_appointment_users

CREATE OR REPLACE FUNCTION get_tcexam_group_appointment_users(pgroupid character varying, pappointmentid character varying)
  RETURNS text AS
$BODY$

DECLARE 
	xoutput text;

BEGIN
SELECT XMLSERIALIZE ( CONTENT
	XMLELEMENT(name "userVOes",
	 xmlagg(
		XMLELEMENT(name "userVO",
184
185
186
187
188
189
		XMLFOREST(u.user_id),
		XMLFOREST(u.user_name  as login),
		XMLFOREST(u.user_email),
		XMLFOREST(u.user_firstname),
		XMLFOREST(u.user_lastname),
		XMLFOREST(u.user_level)
190

191
192
))) as text )
		INTO xoutput
193
194
195
196
		FROM tce_user_groups g
		LEFT JOIN tce_usrgroups ug ON g.group_id = ug.usrgrp_group_id
		LEFT JOIN tce_users u ON u.user_id = ug.usrgrp_user_id
		WHERE g.external_id = concat(pgroupid,':',pappointmentid)
197
198
199
200
201
		;
	RETURN xoutput;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
202
203
204
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
205
ALTER FUNCTION get_tcexam_group_appointment_users(character varying, character varying)
206
  OWNER TO tcexam;
207
208
209
210
211
212
213
214
215
216
217
218


-- get_tcexam_group_users

CREATE OR REPLACE FUNCTION get_tcexam_group_users(group_id character varying)
  RETURNS text AS
$BODY$

SELECT XMLSERIALIZE ( CONTENT
	XMLELEMENT(name "userVOes",
	 xmlagg(
		XMLELEMENT(name "userVO",
219
220
221
222
223
224
225
226
227
228
229
230
231
232
		XMLFOREST(u.user_id),
		XMLFOREST(u.user_name as login),
		XMLFOREST(u.user_email),
		XMLFOREST(u.user_firstname),
		XMLFOREST(u.user_lastname),
		XMLFOREST(u.user_level)
		)
	 )
	) as text )

	FROM tce_user_groups g
	LEFT JOIN tce_usrgroups ug ON g.group_id = ug.usrgrp_group_id
	LEFT JOIN tce_users u ON u.user_id = ug.usrgrp_user_id
	WHERE g.external_id LIKE concat($1,':%');
233
234
235

$BODY$
  LANGUAGE sql VOLATILE
236
237
238
239
240
241
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
ALTER FUNCTION get_tcexam_group_users(character varying)
  OWNER TO tcexam;

242
243
-- get tcexam_user
CREATE OR REPLACE FUNCTION get_tcexam_user(userid character varying)
244
245
 RETURNS text AS 
$function$
246
DECLARE
247
	xoutput text;
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263

BEGIN
SELECT XMLSERIALIZE ( CONTENT
	XMLELEMENT(name "userVOes",
	 xmlagg(
		XMLELEMENT(name "userVO",
		XMLFOREST(u.user_id),
		XMLFOREST(u.user_name  as login),
		XMLFOREST(u.user_email),
		XMLFOREST(u.user_firstname),
		XMLFOREST(u.user_lastname),
		XMLFOREST(u.user_regnumber),
		XMLFOREST(u.user_level)
	))) as text )
INTO xoutput
FROM tce_users u
264
WHERE u.user_name = userid;
265
266
267
RETURN xoutput;
END;
$function$
268
269
270
 LANGUAGE plpgsql VOLATILE
 SECURITY DEFINER
 SET search_path TO tcexam, pg_temp;
Ewald's avatar
Ewald committed
271
ALTER FUNCTION get_tcexam_user(character varying)
272
  OWNER TO tcexam;  
273
274
275

-- insert_tcexam_group

276
CREATE OR REPLACE FUNCTION insert_tcexam_group(pgroupid character varying, pappointmentid character varying, pgroupname character varying)
277
278
279
280
281
  RETURNS void AS
$BODY$
DECLARE
	externalid character varying;
	appointmentid character varying;
282
	groupname character varying;
283
284
	grouptype character varying;
	groupvo xml;
285
286
        
BEGIN
287
	grouptype='T';
288
	externalid = CONCAT (pgroupid,':',pappointmentid);
289
290
	IF pappointmentid = 'owner' THEN
		grouptype = 'B';
291
		pappointmentid = 'Betreuergruppe';
292
	END IF;
293
	groupname = CONCAT (pgroupname ,' - ',pappointmentid);
294
295
	IF NOT EXISTS(SELECT group_id FROM tce_user_groups WHERE external_id = externalid) 
		THEN
296
		INSERT INTO tce_user_groups (group_name, external_id,group_type) VALUES  (groupname,externalid,grouptype);
297
	END IF;
298
299
300

END;
$BODY$
301
  LANGUAGE plpgsql VOLATILE
302
303
304
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
Ewald's avatar
Ewald committed
305
ALTER FUNCTION insert_tcexam_group(character varying, character varying, character varying)
306
  OWNER TO tcexam;
307
308
309
310

 
-- insert_tcexam_user

311
CREATE OR REPLACE FUNCTION insert_tcexam_user(xmlbody text)
312
313
 RETURNS void AS 
$function$
314
    DECLARE
315
316
317
318
319
320
321
	login character varying;
	email character varying;
	firstname character varying;
	lastname character varying;
	matrikelnr character varying;
	uservo xml;

322
	BEGIN
323
		uservo = xmlbody::xml;
324
325
326
327
		SELECT (xpath('/userVO/login/text()', uservo))[1] into login;
		SELECT (xpath('/userVO/email/text()', uservo))[1] into email;
		SELECT (xpath('/userVO/firstName/text()', uservo))[1] into firstname;
		SELECT (xpath('/userVO/lastName/text()', uservo))[1] into lastname;
328
		SELECT (xpath('/userVO/properties/property/name[.="institutionalUserIdentifier"]/../value/text()', uservo))[1] 		into matrikelnr;
329
330

		IF NOT EXISTS(SELECT user_name from tce_users where user_name = login ) 
331
332
333
			THEN
				INSERT INTO tce_users (user_name, user_email, user_firstname, user_lastname, user_regnumber, user_level, user_regdate, user_password, user_ip) 
				VALUES  (login,email,firstname,lastname, matrikelnr, 1, now(), 'no£password', '127.0.0.1');
334
				INSERT INTO tce_usrgroups (usrgrp_user_id,usrgrp_group_id) VALUES ((SELECT user_id FROM tce_users WHERE user_name = login),1);
335
336
337
		END IF;

	END;
338
$function$
339
340
341
 LANGUAGE plpgsql VOLATILE
 SECURITY DEFINER
 SET search_path TO tcexam, pg_temp;
342
ALTER FUNCTION insert_tcexam_user(text)
343
  OWNER TO tcexam;
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367


-- remove_tcexam_user_from_group

CREATE OR REPLACE FUNCTION remove_tcexam_user_from_group(xmlbody text, pgroupid character varying, pappointmentid character varying)
  RETURNS void AS
$BODY$
    DECLARE
	login character varying;	
	userid integer;	
	groupid integer;
        uservo xml; 
        
	BEGIN
		    
		uservo = xmlbody::xml;	
		SELECT (xpath('/userVO/login/text()', uservo))[1] into login;
		SELECT user_id INTO userid FROM tce_users WHERE user_name = login;
		SELECT group_id INTO groupid FROM tce_user_groups WHERE external_id = pgroupid || ':' || pappointmentid;
		DELETE FROM tce_usrgroups WHERE usrgrp_user_id = userid AND usrgrp_group_id = groupid;

	END;
$BODY$
  LANGUAGE plpgsql VOLATILE
368
369
370
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
371
ALTER FUNCTION remove_tcexam_user_from_group(text, character varying, character varying)
372
  OWNER TO tcexam;
373
374


375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
-- Function: set_tcexam_user_level(character varying, integer)

CREATE OR REPLACE FUNCTION set_tcexam_user_level(login character varying, ulevel integer)
  RETURNS void AS
$BODY$

	BEGIN
		UPDATE tce_users SET user_level = ulevel WHERE user_name = login;
	END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
ALTER FUNCTION set_tcexam_user_level(character varying, integer)
  OWNER TO tcexam;


393
394
395
396
397
398
399
400
-- update_tcexam_group


CREATE OR REPLACE FUNCTION update_tcexam_group(pgroupid character varying, pappointmentid character varying, pgroupname character varying)
  RETURNS void AS
$BODY$
DECLARE
       theKey character varying;
401

402
403
404
405
406
BEGIN

	theKey = pgroupid || ':' || pappointmentid;

	IF (SELECT group_name FROM tce_user_groups WHERE external_id = theKey) != pgroupname THEN 
407
		UPDATE tce_user_groups set group_name = CONCAT(pgroupname, ' - ',pappointmentid)  WHERE external_id = theKey;
408
409
410
411
	END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
412
413
414
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
415
ALTER FUNCTION update_tcexam_group(character varying, character varying, character varying)
416
  OWNER TO tcexam;
417
418
419
420
  
  
-- update_tcexam_user

421
CREATE OR REPLACE FUNCTION update_tcexam_user(xmlbody text)
422
423
 RETURNS void AS 
$function$
424
    DECLARE
425
426
427
428
429
430
431
432
	login character varying;
	email character varying;
	firstname character varying;
	lastname character varying;
	matrikelnr character varying;
	userstatus character varying;
	userlevel integer;
	uservo xml;
433

434
	BEGIN
435
436

		uservo = xmlbody::xml;
437
438
439
440
		SELECT (xpath('/userVO/login/text()', uservo))[1] into login;
		SELECT (xpath('/userVO/email/text()', uservo))[1] into email;
		SELECT (xpath('/userVO/firstName/text()', uservo))[1] into firstname;
		SELECT (xpath('/userVO/lastName/text()', uservo))[1] into lastname;
441
442
443
444
445
446
447
448
449
450
		SELECT (xpath('/userVO/properties/property/name[.="institutionalUserIdentifier"]/../value/text()', uservo))[1] into matrikelnr;
		SELECT (xpath('/userVO/status/text()', uservo))[1] into userstatus;
		SELECT user_level FROM tce_users WHERE user_name = login INTO userlevel;

		UPDATE tce_users SET user_email = email WHERE user_name = login;
		UPDATE tce_users SET user_firstname = firstname WHERE user_name = login;
		UPDATE tce_users SET user_lastname = lastname WHERE user_name = login;
		UPDATE tce_users SET user_regnumber = matrikelnr WHERE user_name = login;

		IF (userstatus != 'aktiv')
451
		THEN
452
			UPDATE tce_users SET user_level = 0 WHERE user_name = login;
453
		END IF;
454
455

		IF (userstatus = 'aktiv' AND userlevel = 0)
456
		THEN
457
			UPDATE tce_users SET user_level = 1 WHERE user_name = login;
458
		END IF;
459

460
	END;
461
462

$function$
463

464
465
466
 LANGUAGE plpgsql VOLATILE
 SECURITY DEFINER
 SET search_path TO tcexam, pg_temp;
467
ALTER FUNCTION update_tcexam_user(text)
468
  OWNER TO tcexam;
469

470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
-- Function: get_tcexam_recent_tests(character varying)

-- DROP FUNCTION get_tcexam_recent_tests(character varying);

CREATE OR REPLACE FUNCTION get_tcexam_recent_tests(externalid character varying)
  RETURNS text AS
$BODY$
	DECLARE 
		xoutput text;
	BEGIN
	SELECT XMLSERIALIZE ( CONTENT
		 
		 XMLELEMENT(name "recentTests",
		 xmlagg(
			XMLELEMENT(name "test",
			XMLFOREST(
				t.test_id as "testId",
				t.test_name as "testName",
				u.group_id as "groupId"
				)
			)
		)
		)
		as text )
			INTO xoutput
			FROM tce_user_groups u, tce_testgroups tg, tce_tests t
				WHERE u.group_id  = tg.tstgrp_group_id
				AND u.external_id LIKE concat(externalid,':%') AND u.external_id != concat(externalid,':owner') AND u.email2_sent IS NULL
				AND t.test_id = tg.tstgrp_test_id AND t.test_end_time BETWEEN current_date - 8  AND current_date - 1;
			RETURN xoutput;
	END;
$BODY$
502
503
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  SET search_path = tcexam, pg_temp;
504
ALTER FUNCTION get_tcexam_recent_tests(character varying) OWNER TO tcexam;
505

506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527

-- Function: set_tcexam_email_sent(character varying, integer)

-- DROP FUNCTION set_tcexam_email_sent(character varying, integer);

CREATE OR REPLACE FUNCTION set_tcexam_email_sent(groupid character varying, emailnumber integer)
  RETURNS text AS
$BODY$

DECLARE found text;

BEGIN
	EXECUTE 'SELECT email' || emailnumber || '_sent FROM tce_user_groups WHERE group_id =' || groupid  ||';' INTO found;
	IF found  > '' THEN	
		RETURN '<result>existing</result>';
	ELSE
		EXECUTE 'UPDATE tce_user_groups SET email' || emailnumber || '_sent = current_date WHERE group_id = ' || groupid || ';';
		RETURN '<result>new</result>';	
	END IF;	
END;

$BODY$
528
529
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  SET search_path = tcexam, pg_temp;
530
ALTER FUNCTION set_tcexam_email_sent(character varying, integer)  OWNER TO tcexam;
531
532


533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574


-- Function: count_tcexam_users_by_exam(character varying, character varying)

-- DROP FUNCTION count_tcexam_users_by_exam(character varying, character varying);

CREATE OR REPLACE FUNCTION count_tcexam_users_by_exam(external_group_id character varying, external_appointment_id character varying)
  RETURNS text AS
$BODY$
        DECLARE
		xoutput text;
BEGIN

SELECT XMLSERIALIZE ( CONTENT
	 XMLELEMENT(name "Appointments",
	 xmlagg(
		XMLELEMENT(name "Appointment",
		XMLFOREST(
			u.external_id as "GroupId",
			u.lv as "LV",
			u.testdate as "TestDate",
			u.usercount as "UserCount"
			)
		)
	))
	as text )
		INTO xoutput
		FROM 
		(SELECT foo.external_id, split_part(foo.group_name,' Gruppe 0 - Prüfungstermin: ',1) as lv, split_part(foo.group_name,' Gruppe 0 - Prüfungstermin: ',2) as testdate, count(usrgrp_user_id) as UserCount FROM (
		SELECT * FROM tce_usrgroups g
		LEFT JOIN tce_user_groups ug ON ug.group_id = g.usrgrp_group_id
		WHERE ug.external_id LIKE CONCAT($1,':',$2) AND ug.external_id NOT LIKE ('%owner%')
		) 
		AS foo
		GROUP BY foo.external_id, foo.group_name
		ORDER BY foo.external_id) as u
		; 
	RETURN xoutput;
END;
	

$BODY$
575
576
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  SET search_path = tcexam, pg_temp;
577
ALTER FUNCTION count_tcexam_users_by_exam(character varying, character varying)  OWNER TO tcexam;
578

579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600

-- Function: get_tcexam_groupid(character varying, character varying)

-- DROP FUNCTION get_tcexam_groupid(character varying, character varying);

CREATE OR REPLACE FUNCTION get_tcexam_groupid(pgroupid character varying, pappointmentid character varying)
  RETURNS text AS
$BODY$
DECLARE
	groupid text;        
BEGIN
SELECT XMLSERIALIZE ( CONTENT
	 
	XMLFOREST(
		group_id as "groupId")
	
	as text )
		INTO groupid
		FROM tce_user_groups WHERE external_id = CONCAT(pgroupid,':',pappointmentid) ;
	RETURN groupid;	
END;
$BODY$
601
602
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  SET search_path = tcexam, pg_temp;
603
ALTER FUNCTION get_tcexam_groupid(character varying, character varying)  OWNER TO tcexam;
604

605

606
  
607
608
609
610
611
-- create esb user

-- CREATE USER esb WITH PASSWORD '....';

-- grant execute right on the functions
612
613
--
-- # Generated with:
614
-- # grep 'CREATE OR REPLACE FUNCTION' postgres_db_structure_functions.sql | sed 's/CREATE OR REPLACE FUNCTION //' | while read f; do echo "GRANT EXECUTE ON FUNCTION $f TO esb;"; done
615
616

GRANT EXECUTE ON FUNCTION assign_tcexam_user_to_group(xmlbody text, pgroupid character varying, pappointmentid character varying) TO esb;
617
GRANT EXECUTE ON FUNCTION count_tcexam_users_by_exam(external_group_id character varying, external_appointment_id character varying) TO esb;
618
619
620
GRANT EXECUTE ON FUNCTION get_tcexam_group(externalid character varying) TO esb;
GRANT EXECUTE ON FUNCTION get_tcexam_group_appointment_users(pgroupid character varying, pappointmentid character varying) TO esb;
GRANT EXECUTE ON FUNCTION get_tcexam_group_users(group_id character varying) TO esb;
621
622
GRANT EXECUTE ON FUNCTION get_tcexam_groupid(pgroupid character varying, pappointmentid character varying) TO esb;
GRANT EXECUTE ON FUNCTION get_tcexam_recent_tests(externalid character varying) TO esb;
623
GRANT EXECUTE ON FUNCTION get_tcexam_user(userid character varying) TO esb;
624
GRANT EXECUTE ON FUNCTION insert_tcexam_group(pgroupid character varying, pappointmentid character varying, pgroupname character varying) TO esb;
625
GRANT EXECUTE ON FUNCTION insert_tcexam_user(xmlbody text) TO esb;
626
GRANT EXECUTE ON FUNCTION remove_tcexam_user_from_group(xmlbody text, pgroupid character varying, pappointmentid character varying) TO esb;
627
GRANT EXECUTE ON FUNCTION set_tcexam_email_sent(groupid character varying, emailnumber integer) TO esb;
628
GRANT EXECUTE ON FUNCTION set_tcexam_user_level(login character varying, ulevel integer) TO esb;
629
GRANT EXECUTE ON FUNCTION update_tcexam_group(pgroupid character varying, pappointmentid character varying, pgroupname character varying) TO esb;
630
GRANT EXECUTE ON FUNCTION update_tcexam_user(xmlbody text) TO esb;
631