HI, all
This following procedure will cause two problems
1) If the procedure fails(which is to raise an exception inside it) and re-do again, sometimes the temp1 will exist and cause the first query fails.
2) After doing this procedure several times, it may cause an error about 2nd query said OID something could not find.
But after a while since error happened, it will back to work again!
-----
-
CREATE OR REPLACE FUNCTION public.jobp_create_jobs(xuser_id varchar, varchar, varchar) RETURNS integer AS
-
$BODY$
-
DECLARE
-
xgroup varchar := $2;
-
xclass varchar := $3;
-
s1 text;
-
jcnt integer;
-
BEGIN
-
xgroup := comp_quote_str_list(xgroup);
-
xclass := comp_quote_str_list(xclass);
-
s1 := 'create temp table temp1 on commit drop as select a.job_name from job_info a where a.group_id in ('
-
|| xgroup || ') and a.confidential_class in ('
-
|| xclass || ')and not exists (select b.job_name from job_user2 b where b.user_id ='
-
|| quote_literal(xuser_id) || ' and a.job_name = b.job_name)';
-
-
-- RAISE NOTICE '%',s1;
-
EXECUTE s1;
-
-
insert into job_user2 (user_id, job_name) select
-
xuser_id, b.job_name from temp1 b;
-
GET DIAGNOSTICS jcnt = ROW_COUNT;
-
IF jcnt = 0 THEN
-
RAISE EXCEPTION 'No any job inserted';
-
END IF;
-
-
-- RAISE EXCEPTION 'check if it rolled back';
-
-
RETURN jcnt;
-
END;$BODY$
-
LANGUAGE 'plpgsql' VOLATILE;
