By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,337 Members | 2,084 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,337 IT Pros & Developers. It's quick & easy.

questions about stored procedure

P: 51
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!
-----
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION public.jobp_create_jobs(xuser_id varchar, varchar, varchar) RETURNS integer AS
  2. $BODY$
  3. DECLARE
  4.    xgroup varchar := $2;
  5.    xclass varchar := $3;
  6.    s1  text;
  7.    jcnt integer;
  8. BEGIN
  9.    xgroup := comp_quote_str_list(xgroup);
  10.     xclass := comp_quote_str_list(xclass);
  11.     s1 := 'create temp table temp1 on commit drop as select a.job_name from job_info a where a.group_id in ('
  12.        || xgroup || ') and a.confidential_class in ('
  13.        || xclass || ')and not exists (select b.job_name from job_user2 b where b.user_id ='
  14.         || quote_literal(xuser_id) || ' and a.job_name = b.job_name)';
  15.  
  16. -- RAISE NOTICE '%',s1;
  17.     EXECUTE s1;
  18.  
  19.     insert into job_user2 (user_id, job_name) select 
  20.        xuser_id, b.job_name from temp1 b;
  21.     GET DIAGNOSTICS jcnt = ROW_COUNT;
  22.     IF jcnt = 0 THEN
  23.         RAISE EXCEPTION 'No any job inserted';
  24.     END IF;
  25.  
  26. -- RAISE EXCEPTION 'check if it rolled back';
  27.  
  28.     RETURN jcnt;
  29.  END;$BODY$
  30.   LANGUAGE 'plpgsql' VOLATILE;
Apr 8 '09 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.