我正在尝试在此函数上添加一个日期字符串，因此我希望只获取最近 7 天的所有记录，而不是获取所有记录。
CREATE OR REPLACE FUNCTION public.customerOrders(_customer_id integer, _startperiod timestamp with time zone, _endperiod timestamp with time zone, _sort_field_and_direction character varying, _limit integer, _offset integer, OUT id integer, OUT customerid integer, OUT description character varying, OUT placedon timestamp with time zone)
RETURNS SETOF record
LANGUAGE plpgsql
AS $function$
DECLARE
f_string TEXT;
f_max_rows INTEGER := 100;
BEGIN
f_string := '';
f_string := 'WITH
limited_orders AS (SELECT * FROM customerorder
WHERE customer_id = ' || _customer_id || '
ORDER BY order_id DESC
LIMIT ' || f_max_rows || '
),
orders AS(
SELECT order_id, customer_id, order_placed_on,order_description
FROM limited_orders
WHERE customer_id = ' || _customer_id || '
GROUP BY order_id, customer_id,order_placed_on,order_description
)
SELECT order_id as id, customer_id as customerId, order_description as description, order_placed_on as placedOn
FROM customerorder
where
(order_placed_on >= ''%s'' AND order_placed_on <= ''%s'')
ORDER BY ' || _sort_field_and_direction || ' LIMIT ' || _limit || ' OFFSET ' || _offset;
raise notice '%', f_string;
RETURN QUERY
EXECUTE FORMAT(f_string, _startperiod, _endperiod);
END;
$function$
;
目前如果我调用函数
SELECT * FROM public.customerOrders('2579927','2022-10-01'::date,'2022-10-05'::date,'placedOn DESC','50','0')
该功能按预期工作。但是，我想要实现的是_startPeriod和_endPeriod要么是 30 天的默认值，要么是_startPeriod较早的日期（从今天起 30 天），要么是今天（例如_endPeriodcurrent_date 或）。now()
我试过在下面声明 a_startperiod和_endperiodlike 。
CREATE OR REPLACE FUNCTION public.customerOrders1(_customer_id integer, _sort_field_and_direction character varying, _limit integer, _offset integer, OUT id integer, OUT customerid integer, OUT description character varying, OUT placedon timestamp with time zone)
RETURNS SETOF record
LANGUAGE plpgsql
AS $function$
DECLARE
f_string TEXT;
f_max_rows INTEGER := 100;
_startPeriod DATE;
_endPeriod DATE;
begin
_startperiod := 'select current_date - 30';
_endPeriod := 'select current_date';
f_string := '';
f_string := 'WITH
limited_orders AS (SELECT * FROM customerorder
WHERE customer_id = ' || _customer_id || '
ORDER BY order_id DESC
LIMIT ' || f_max_rows || '
),
orders AS(
SELECT order_id, customer_id, order_placed_on,order_description
FROM limited_orders
WHERE customer_id = ' || _customer_id || '
GROUP BY order_id, customer_id,order_placed_on,order_description
)
SELECT order_id as id, customer_id as customerId, order_description as description, order_placed_on as placedOn
FROM customerorder
where
(order_placed_on >= ''%s'' AND order_placed_on <= ''%s'')
ORDER BY ' || _sort_field_and_direction || ' LIMIT ' || _limit || ' OFFSET ' || _offset;
raise notice '%', f_string;
RETURN QUERY
EXECUTE FORMAT(f_string, _startperiod, _endperiod);
END;
$function$
;
我试图默认它，所以开始时间是 30 天前，结束时间是今天，但是当我去运行新功能时。
SELECT * FROM public.customerOrders1('2579927','placedOn DESC','50','0');
我得到：
ERROR: invalid input syntax for type date: "select current_date - 30"
有更好的方法吗？
理想情况下，我希望 startPeriod 和 endPeriod 在调用函数时允许输入，但如果没有添加输入，则默认为最近 30 天。
