/sql/Function/reports_set_add_info.sql

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