CREATE OR REPLACE FUNCTION "public"."new_three_choicesubject"("p_province" varchar, "p_choose_subject1" varchar, "p_choose_subject2" varchar, "p_choose_subject3" varchar)
RETURNS "pg_catalog"."refcursor" AS $BODY$
declare user_list refcursor;
BEGIN
open user_list for
-- 或和單關系
select * from sf_choose_subject where province = p_province and second_subject_attr = '或' or second_subject_attr = '單' and
(choose_subject1 = p_choose_subject1 or choose_subject1 = p_choose_subject2 or choose_subject1 = p_choose_subject3) and
(choose_subject2 = p_choose_subject1 or choose_subject2 = p_choose_subject2 or choose_subject2 = p_choose_subject3) and
(choose_subject3 = p_choose_subject1 or choose_subject3 = p_choose_subject2 or choose_subject3 = p_choose_subject3)
union
-- 無關系
select * from sf_choose_subject where province = p_province and second_subject_attr = '無'
union
-- 3與關系
select * from sf_choose_subject where province = p_province and second_subject_attr = '與' and
((choose_subject1 = p_choose_subject1 and choose_subject2 = p_choose_subject3 and choose_subject3 = p_choose_subject2) or
(choose_subject1 = p_choose_subject3 and choose_subject2 = p_choose_subject1 and choose_subject3 = p_choose_subject2) or
(choose_subject1 = p_choose_subject2 and choose_subject2 = p_choose_subject3 and choose_subject3 = p_choose_subject1) or
(choose_subject1 = p_choose_subject3 and choose_subject2 = p_choose_subject2 and choose_subject3 = p_choose_subject1) or
(choose_subject1 = p_choose_subject1 and choose_subject2 = p_choose_subject2 and choose_subject3 = p_choose_subject3) or
(choose_subject1 = p_choose_subject2 and choose_subject2 = p_choose_subject1 and choose_subject3 = p_choose_subject3))
union
-- 2與關系
select * from sf_choose_subject where province = p_province and second_subject_attr = '與' and
((choose_subject1 = p_choose_subject1 and choose_subject2 = p_choose_subject3) or
(choose_subject1 = p_choose_subject3 and choose_subject2 = p_choose_subject1) or
(choose_subject1 = p_choose_subject2 and choose_subject2 = p_choose_subject3) or
(choose_subject1 = p_choose_subject3 and choose_subject2 = p_choose_subject2) or
(choose_subject1 = p_choose_subject1 and choose_subject2 = p_choose_subject2) or
(choose_subject1 = p_choose_subject2 and choose_subject2 = p_choose_subject1)) and (choose_subject3 is null or choose_subject3 = '');
close user_list;
RETURN user_list;
END$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
uj5u.com熱心網友回復：
不太會寫。。。無法發現錯誤。uj5u.com熱心網友回復：
不能在函式體里close user_list，在呼叫函式后再close轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/7117.html
標籤：PostgreSQL