/sql/Function/reports_set_exec_print_fio.sql

 1--DROP FUNCTION reports_set_exec_print_fio(integer,character varying,character varying);
 2CREATE OR REPLACE FUNCTION reports_set_exec_print_fio(integer,character varying,character varying,character varying)
 3  RETURNS INTEGER AS
 4$BODY$
 5    DECLARE
 6     find_id INTEGER;
 7     exec_id INTEGER;
 8     rep_id     ALIAS for $1;
 9     user_fio 	ALIAS for $2;
10     user_type  ALIAS for $3;
11     phone	ALIAS for $4;
12    BEGIN                                                                                                 
13    
14	find_id = (SELECT id FROM reports WHERE id = rep_id);
15	IF (find_id IS NULL) THEN
16		INSERT INTO debug_log (inf_str) VALUES ('?????? ??????? ??????. ? ??????? reports ?? ?????????? ?????? ? ??????????? ??  ??????? cups_data_log ?????? N='||rep_id);
17		RETURN 0;
18	END IF;
19     BEGIN	
20	IF (user_type='executor') THEN	
21		-- set_executor_fio
22		exec_id = (SELECT id FROM executors WHERE fio = user_fio and telephone =phone);
23		IF (exec_id IS NULL) THEN
24			INSERT INTO executors (fio,telephone) VALUES (user_fio,phone);
25			exec_id = (SELECT id FROM executors WHERE fio = user_fio and telephone = phone);
26		END IF;	
27		UPDATE reports SET executor_id = exec_id WHERE reports.id = find_id;
28	END IF;
29	
30	IF (user_type='pressman') THEN
31		-- set_executor_fio
32		exec_id = (SELECT id FROM pressman WHERE fio = user_fio);
33		IF (exec_id IS NULL) THEN
34			INSERT INTO pressman (fio) VALUES (user_fio);
35			exec_id = (SELECT id FROM pressman WHERE fio = user_fio);
36		END IF;	
37		UPDATE reports SET pressman_id = exec_id WHERE reports.id = find_id;	
38	END IF;
39	RETURN 1;
40     EXCEPTION WHEN unique_violation THEN
41	RETURN 0; 		-- do nothing, and loop to try the UPDATE again
42     END;
43    END;
44$BODY$
45  LANGUAGE 'plpgsql' VOLATILE
46  COST 100;
47ALTER FUNCTION reports_set_exec_print_fio(integer,character varying,character varying,character varying) OWNER TO postgres;