pgsql的例子

DROP FUNCTION mail_condition(varchar,int4,varchar);
CREATE OR REPLACE FUNCTION mail_condition(varchar,int4,varchar)
RETURNS varchar AS '
DECLARE
cust_list_rec RECORD;
answer_profile_rec RECORD;
cst_id_rec RECORD;
userid_check_rec RECORD;
userid_check_sql varchar;
cust_list_sql varchar;
cst_id_sql varchar;
answer_profile_sql varchar;
lword varchar := '''';
rword varchar := '''';
condition varchar := '''';
pos integer NOT NULL DEFAULT 0;
pos2 int4 := 0;
leftdata varchar;
rightdata varchar;
len int4;
i int4 := 1;
userid_cnt int4 := 0;
BEGIN
-- del record when type = 2
delete from cust_targetlist where type = $2;
-- get condition
cust_list_sql := ''select * from cust_list_condition where account=''''''||$1||'''''' and enq_xml=''''''||$3||'''''' and question_id= '' || ''''''XXXX9'''''' || '''';
FOR cust_list_rec IN EXECUTE cust_list_sql LOOP
IF cust_list_rec.type=7 or cust_list_rec.type=8 or cust_list_rec.type=9 or cust_list_rec.type=10 or cust_list_rec.type=11 or cust_list_rec.type=12 THEN
IF i > 0 THEN
answer_profile_sql := ''select distinct a.id, p.question_id from cust_answer_admin a, cust_answer_profile p where a.id = p.id and a.enq_xml = ''''''||cust_list_rec.enq_xml ||'''''' '';
pos = position('','' in cust_list_rec.data);
IF pos > 0 THEN
lword := substr(cust_list_rec.data, 1, pos-1);
rword := substr(cust_list_rec.data, pos+1);
--rword := substr(tword,1);
IF length(lword) > 0 AND length(rword) > 0 THEN
condition := lword || ''@'' || rword;
condition := '' and p.prof_char='''''' || condition || '''''''';
END IF;
IF length(lword) > 0 AND length(rword) = 0 THEN
condition := '' and p.prof_char like '''''' || lword || ''%@%'''''';
END IF;
IF length(rword) > 0 AND length(lword) = 0 THEN
condition := '' and p.prof_char like '''''' || ''%@%'' || rword || '''''''';
END IF;
answer_profile_sql := answer_profile_sql || condition;
END IF;
FOR answer_profile_rec IN EXECUTE answer_profile_sql LOOP
userid_check_sql := ''select id from cust_targetlist where id='' || answer_profile_rec.id || '' and type='' || $2 || '' and resultflag=1'';
FOR userid_check_rec IN EXECUTE userid_check_sql LOOP
userid_cnt := userid_cnt + 1;
END LOOP;
IF userid_cnt = 0 THEN
insert into cust_targetlist values (answer_profile_rec.id,$2,1);
END IF;
END LOOP;
ELSE
return i;
END IF;
END IF;
i := i + 1;
END LOOP;
IF i = 1 THEN
cst_id_sql := ''select id from cust_answer_admin where invalid=0 and enq_xml= ''''''||$3||'''''''';
FOR cst_id_rec IN EXECUTE cst_id_sql LOOP
insert into cust_targetlist values (cst_id_rec.id,$2,1);
END LOOP;
return 1;
END IF;
return i;
END;
'

LANGUAGE 'plpgsql' VOLATILE;