希望实现如下功能:
CREATE OR REPLACE FUNCTION calendar_list(character,timestamp,timestamp,character,timestamp,timestamp,character)
RETURNS SETOF goods_schedule AS
$BODY$
DECLARE
--カレンダーを表示する
r goods_schedule%rowtype;
BEGIN
FOR r IN SELECT reserve_type, goods_id, status, color_bar, open_flg
FROM goods_schedule,car_schedule
WHERE
------------------------------------------------------
--我想把下边的条件写成如果$1是空的话就不考虑那个条件--
--但是我不会判断,请高手指教,关怀,教导,教育--
------------------------------------------------------
goods_schedule.reserve_id=$1 AND
goods_schedule.res_out_date>=$2 AND
goods_schedule.res_bak_date<=$3 AND
goods_schedule.inputor=$4 AND
goods_schedule.res_out_date>=$5 AND
goods_schedule.res_bak_date<=$6 AND
car_schedule.car_id=$7
LOOP
-- can do some processing here
RETURN NEXT r; -- return current row of SELECT
END LOOP;
RETURN ;
END
$BODY$
LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION calendar_list(character,timestamp,timestamp,character,timestamp,timestamp,character)
RETURNS SETOF goods_schedule AS
$BODY$
DECLARE
--カレンダーを表示する
r goods_schedule%rowtype;
BEGIN
FOR r IN SELECT reserve_type, goods_id, status, color_bar, open_flg
FROM goods_schedule,car_schedule
WHERE
------------------------------------------------------
--我想把下边的条件写成如果$1是空的话就不考虑那个条件--
--但是我不会判断,请高手指教,关怀,教导,教育--
------------------------------------------------------
goods_schedule.reserve_id=$1 AND
goods_schedule.res_out_date>=$2 AND
goods_schedule.res_bak_date<=$3 AND
goods_schedule.inputor=$4 AND
goods_schedule.res_out_date>=$5 AND
goods_schedule.res_bak_date<=$6 AND
car_schedule.car_id=$7
LOOP
-- can do some processing here
RETURN NEXT r; -- return current row of SELECT
END LOOP;
RETURN ;
END
$BODY$
LANGUAGE 'plpgsql';
还差下文。
那若是空的话,结果集是否过滤掉?。
若不过滤掉,改为
goods_schedule.reserve_id is null or
(goods_schedule.reserve_id=$1 AND
goods_schedule.res_out_date>=$2 AND
goods_schedule.res_bak_date <=$3 AND
goods_schedule.inputor=$4 AND
goods_schedule.res_out_date>=$5 AND
goods_schedule.res_bak_date <=$6 AND
car_schedule.car_id=$7 )若过滤掉,这个语句就不用改了.
但我想要的是如果条件$1==''就不用goods_schedule.reserve_id=$1
如果条件$1!=''就用goods_schedule.reserve_id=$1
谢谢楼上帮忙
AND
goods_schedule.res_out_date>=$2 AND
goods_schedule.res_bak_date <=$3 AND
goods_schedule.inputor=$4 AND
goods_schedule.res_out_date>=$5 AND
goods_schedule.res_bak_date <=$6 AND
car_schedule.car_id=$7这样满足你要求了吧