postgres_db_structure_functions.sql 18.8 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
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
-- DROP FUNCTION get_admin_group(bigint, bigint);

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'
		into xgroup_id;
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);
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' into xgroup_id;
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;
71

72
-- functions for tcexam esb integration
73
74
75
76
77
78
79
80

-- 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;
81
82
83
84
85
	externalid character varying;
	userid bigint;
	groupid bigint;
	studykey character varying;
	uservo xml; 
86
        
87
88
89
    BEGIN

	uservo = xmlbody::xml;
90
91
	externalid = CONCAT (pgroupid,':',pappointmentid);
	SELECT (xpath('/userVO/login/text()', uservo))[1] into login;
92
	SELECT (xpath('/userVO/appointmentStudyKey/text()', uservo))[1] into studykey;
93

94
95
	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;
96

97
98
99
100
	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;
101
102
103
104

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


--  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
150
151
152
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
153
ALTER FUNCTION get_tcexam_group(character varying)
154
  OWNER TO tcexam;
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170


-- 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",
171
172
173
174
175
176
		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)
177

178
179
))) as text )
		INTO xoutput
180
181
182
183
		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)
184
185
186
187
188
		;
	RETURN xoutput;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
189
190
191
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
192
ALTER FUNCTION get_tcexam_group_appointment_users(character varying, character varying)
193
  OWNER TO tcexam;
194
195
196
197
198
199
200
201
202
203
204
205


-- 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",
206
207
208
209
210
211
212
213
214
215
216
217
218
219
		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,':%');
220
221
222

$BODY$
  LANGUAGE sql VOLATILE
223
224
225
226
227
228
  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;

229
230
-- get tcexam_user
CREATE OR REPLACE FUNCTION get_tcexam_user(userid character varying)
231
232
 RETURNS text AS 
$function$
233
DECLARE
234
	xoutput text;
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250

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
251
WHERE u.user_name = userid;
252
253
254
RETURN xoutput;
END;
$function$
255
256
257
 LANGUAGE plpgsql VOLATILE
 SECURITY DEFINER
 SET search_path TO tcexam, pg_temp;
Ewald's avatar
Ewald committed
258
ALTER FUNCTION get_tcexam_user(character varying)
259
  OWNER TO tcexam;  
260
261
262

-- insert_tcexam_group

263
CREATE OR REPLACE FUNCTION insert_tcexam_group(pgroupid character varying, pappointmentid character varying, pgroupname character varying)
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
  RETURNS void AS
$BODY$
DECLARE
	externalid character varying;
	appointmentid character varying;
        groupvo xml;  
        
BEGIN
            
	externalid = CONCAT (pgroupid,':',pappointmentid);
		
	IF NOT EXISTS(SELECT group_id FROM tce_user_groups WHERE external_id = externalid) 
		THEN
		INSERT INTO tce_user_groups (group_name, external_id) VALUES  (pgroupname,externalid);
	END IF;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
283
284
285
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
Ewald's avatar
Ewald committed
286
ALTER FUNCTION insert_tcexam_group(character varying, character varying, character varying)
287
  OWNER TO tcexam;
288
289
290
291

 
-- insert_tcexam_user

292
CREATE OR REPLACE FUNCTION insert_tcexam_user(xmlbody text)
293
294
 RETURNS void AS 
$function$
295
    DECLARE
296
297
298
299
300
301
302
	login character varying;
	email character varying;
	firstname character varying;
	lastname character varying;
	matrikelnr character varying;
	uservo xml;

303
	BEGIN
304
		uservo = xmlbody::xml;
305
306
307
308
		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;
309
		SELECT (xpath('/userVO/properties/property/name[.="institutionalUserIdentifier"]/../value/text()', uservo))[1] 		into matrikelnr;
310
311

		IF NOT EXISTS(SELECT user_name from tce_users where user_name = login ) 
312
313
314
			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');
315
				INSERT INTO tce_usrgroups (usrgrp_user_id,usrgrp_group_id) VALUES ((SELECT user_id FROM tce_users WHERE user_name = login),1);
316
317
318
		END IF;

	END;
319
$function$
320
321
322
 LANGUAGE plpgsql VOLATILE
 SECURITY DEFINER
 SET search_path TO tcexam, pg_temp;
323
ALTER FUNCTION insert_tcexam_user(text)
324
  OWNER TO tcexam;
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348


-- 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
349
350
351
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
352
ALTER FUNCTION remove_tcexam_user_from_group(text, character varying, character varying)
353
  OWNER TO tcexam;
354
355


356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
-- 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;


374
375
376
377
378
379
380
381
-- 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;
382

383
384
385
386
387
388
389
390
391
392
BEGIN

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

	IF (SELECT group_name FROM tce_user_groups WHERE external_id = theKey) != pgroupname THEN 
		UPDATE tce_user_groups set group_name = pgroupname WHERE external_id = theKey;
	END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
393
394
395
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
396
ALTER FUNCTION update_tcexam_group(character varying, character varying, character varying)
397
  OWNER TO tcexam;
398
399
400
401
  
  
-- update_tcexam_user

402
CREATE OR REPLACE FUNCTION update_tcexam_user(xmlbody text)
403
404
 RETURNS void AS 
$function$
405
    DECLARE
406
407
408
409
410
411
412
413
	login character varying;
	email character varying;
	firstname character varying;
	lastname character varying;
	matrikelnr character varying;
	userstatus character varying;
	userlevel integer;
	uservo xml;
414

415
	BEGIN
416
417

		uservo = xmlbody::xml;
418
419
420
421
		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;
422
423
424
425
426
427
428
429
430
431
		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')
432
		THEN
433
			UPDATE tce_users SET user_level = 0 WHERE user_name = login;
434
		END IF;
435
436

		IF (userstatus = 'aktiv' AND userlevel = 0)
437
		THEN
438
			UPDATE tce_users SET user_level = 1 WHERE user_name = login;
439
		END IF;
440

441
	END;
442
443

$function$
444

445
446
447
 LANGUAGE plpgsql VOLATILE
 SECURITY DEFINER
 SET search_path TO tcexam, pg_temp;
448
ALTER FUNCTION update_tcexam_user(text)
449
  OWNER TO tcexam;
450

451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
-- 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$
483
484
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  SET search_path = tcexam, pg_temp;
485
ALTER FUNCTION get_tcexam_recent_tests(character varying) OWNER TO tcexam;
486

487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508

-- 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$
509
510
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  SET search_path = tcexam, pg_temp;
511
ALTER FUNCTION set_tcexam_email_sent(character varying, integer)  OWNER TO tcexam;
512
513


514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
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


-- 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$
556
557
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  SET search_path = tcexam, pg_temp;
558
ALTER FUNCTION count_tcexam_users_by_exam(character varying, character varying)  OWNER TO tcexam;
559

560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581

-- 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$
582
583
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  SET search_path = tcexam, pg_temp;
584
ALTER FUNCTION get_tcexam_groupid(character varying, character varying)  OWNER TO tcexam;
585

586

587
  
588
589
590
591
592
-- create esb user

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

-- grant execute right on the functions
593
594
--
-- # Generated with:
595
-- # 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
596
597

GRANT EXECUTE ON FUNCTION assign_tcexam_user_to_group(xmlbody text, pgroupid character varying, pappointmentid character varying) TO esb;
598
GRANT EXECUTE ON FUNCTION count_tcexam_users_by_exam(external_group_id character varying, external_appointment_id character varying) TO esb;
599
600
601
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;
602
603
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;
604
GRANT EXECUTE ON FUNCTION get_tcexam_user(userid character varying) TO esb;
605
GRANT EXECUTE ON FUNCTION insert_tcexam_group(pgroupid character varying, pappointmentid character varying, pgroupname character varying) TO esb;
606
GRANT EXECUTE ON FUNCTION insert_tcexam_user(xmlbody text) TO esb;
607
GRANT EXECUTE ON FUNCTION remove_tcexam_user_from_group(xmlbody text, pgroupid character varying, pappointmentid character varying) TO esb;
608
GRANT EXECUTE ON FUNCTION set_tcexam_email_sent(groupid character varying, emailnumber integer) TO esb;
609
GRANT EXECUTE ON FUNCTION set_tcexam_user_level(login character varying, ulevel integer) TO esb;
610
GRANT EXECUTE ON FUNCTION update_tcexam_group(pgroupid character varying, pappointmentid character varying, pgroupname character varying) TO esb;
611
GRANT EXECUTE ON FUNCTION update_tcexam_user(xmlbody text) TO esb;
612