postgres_db_structure_functions.sql 19 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
86
87
88
89






90
-- functions for tcexam esb integration
91
92
93
94
95
96
97
98

-- 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;
99
100
101
102
103
	externalid character varying;
	userid bigint;
	groupid bigint;
	studykey character varying;
	uservo xml; 
104
        
105
106
107
    BEGIN

	uservo = xmlbody::xml;
108
109
	externalid = CONCAT (pgroupid,':',pappointmentid);
	SELECT (xpath('/userVO/login/text()', uservo))[1] into login;
110
	SELECT (xpath('/userVO/appointmentStudyKey/text()', uservo))[1] into studykey;
111

112
113
	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;
114

115
116
117
118
	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;
119
120
121
122

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


--  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
168
169
170
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
171
ALTER FUNCTION get_tcexam_group(character varying)
172
  OWNER TO tcexam;
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188


-- 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",
189
190
191
192
193
194
		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)
195

196
197
))) as text )
		INTO xoutput
198
199
200
201
		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)
202
203
204
205
206
		;
	RETURN xoutput;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
207
208
209
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
210
ALTER FUNCTION get_tcexam_group_appointment_users(character varying, character varying)
211
  OWNER TO tcexam;
212
213
214
215
216
217
218
219
220
221
222
223


-- 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",
224
225
226
227
228
229
230
231
232
233
234
235
236
237
		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,':%');
238
239
240

$BODY$
  LANGUAGE sql VOLATILE
241
242
243
244
245
246
  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;

247
248
-- get tcexam_user
CREATE OR REPLACE FUNCTION get_tcexam_user(userid character varying)
249
250
 RETURNS text AS 
$function$
251
DECLARE
252
	xoutput text;
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268

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
269
WHERE u.user_name = userid;
270
271
272
RETURN xoutput;
END;
$function$
273
274
275
 LANGUAGE plpgsql VOLATILE
 SECURITY DEFINER
 SET search_path TO tcexam, pg_temp;
Ewald's avatar
Ewald committed
276
ALTER FUNCTION get_tcexam_user(character varying)
277
  OWNER TO tcexam;  
278
279
280

-- insert_tcexam_group

281
CREATE OR REPLACE FUNCTION insert_tcexam_group(pgroupid character varying, pappointmentid character varying, pgroupname character varying)
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
  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
301
302
303
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
Ewald's avatar
Ewald committed
304
ALTER FUNCTION insert_tcexam_group(character varying, character varying, character varying)
305
  OWNER TO tcexam;
306
307
308
309

 
-- insert_tcexam_user

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

321
	BEGIN
322
		uservo = xmlbody::xml;
323
324
325
326
		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;
327
		SELECT (xpath('/userVO/properties/property/name[.="institutionalUserIdentifier"]/../value/text()', uservo))[1] 		into matrikelnr;
328
329

		IF NOT EXISTS(SELECT user_name from tce_users where user_name = login ) 
330
331
332
			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');
333
				INSERT INTO tce_usrgroups (usrgrp_user_id,usrgrp_group_id) VALUES ((SELECT user_id FROM tce_users WHERE user_name = login),1);
334
335
336
		END IF;

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


-- 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
367
368
369
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
370
ALTER FUNCTION remove_tcexam_user_from_group(text, character varying, character varying)
371
  OWNER TO tcexam;
372
373


374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
-- 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;


392
393
394
395
396
397
398
399
-- 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;
400

401
402
403
404
405
406
407
408
409
410
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
411
412
413
  SECURITY DEFINER
  -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
  SET search_path = tcexam, pg_temp;
414
ALTER FUNCTION update_tcexam_group(character varying, character varying, character varying)
415
  OWNER TO tcexam;
416
417
418
419
  
  
-- update_tcexam_user

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

433
	BEGIN
434
435

		uservo = xmlbody::xml;
436
437
438
439
		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;
440
441
442
443
444
445
446
447
448
449
		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')
450
		THEN
451
			UPDATE tce_users SET user_level = 0 WHERE user_name = login;
452
		END IF;
453
454

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

459
	END;
460
461

$function$
462

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

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
-- 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$
501
502
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  SET search_path = tcexam, pg_temp;
503
ALTER FUNCTION get_tcexam_recent_tests(character varying) OWNER TO tcexam;
504

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

-- 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$
527
528
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  SET search_path = tcexam, pg_temp;
529
ALTER FUNCTION set_tcexam_email_sent(character varying, integer)  OWNER TO tcexam;
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
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573


-- 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$
574
575
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  SET search_path = tcexam, pg_temp;
576
ALTER FUNCTION count_tcexam_users_by_exam(character varying, character varying)  OWNER TO tcexam;
577

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

-- 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$
600
601
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  SET search_path = tcexam, pg_temp;
602
ALTER FUNCTION get_tcexam_groupid(character varying, character varying)  OWNER TO tcexam;
603

604

605
  
606
607
608
609
610
-- create esb user

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

-- grant execute right on the functions
611
612
--
-- # Generated with:
613
-- # 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
614
615

GRANT EXECUTE ON FUNCTION assign_tcexam_user_to_group(xmlbody text, pgroupid character varying, pappointmentid character varying) TO esb;
616
GRANT EXECUTE ON FUNCTION count_tcexam_users_by_exam(external_group_id character varying, external_appointment_id character varying) TO esb;
617
618
619
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;
620
621
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;
622
GRANT EXECUTE ON FUNCTION get_tcexam_user(userid character varying) TO esb;
623
GRANT EXECUTE ON FUNCTION insert_tcexam_group(pgroupid character varying, pappointmentid character varying, pgroupname character varying) TO esb;
624
GRANT EXECUTE ON FUNCTION insert_tcexam_user(xmlbody text) TO esb;
625
GRANT EXECUTE ON FUNCTION remove_tcexam_user_from_group(xmlbody text, pgroupid character varying, pappointmentid character varying) TO esb;
626
GRANT EXECUTE ON FUNCTION set_tcexam_email_sent(groupid character varying, emailnumber integer) TO esb;
627
GRANT EXECUTE ON FUNCTION set_tcexam_user_level(login character varying, ulevel integer) TO esb;
628
GRANT EXECUTE ON FUNCTION update_tcexam_group(pgroupid character varying, pappointmentid character varying, pgroupname character varying) TO esb;
629
GRANT EXECUTE ON FUNCTION update_tcexam_user(xmlbody text) TO esb;
630