PostgreSQL函数中timestampz字段查询返回空结果集问题求助

我调用的函数语句：

SELECT * FROM f_loop_in_lockstep_final('{454,454}'::int[]
                             , '{2,3}'::int[], to_date('2023-01-17','YYYY-MM-DD'));

函数定义：

CREATE or replace FUNCTION f_loop_in_lockstep_final(_id_arr int[], _counter_arr int[], d_date date)

函数体内的WHERE子句（简化版）：

select * from routes where
    routes.time_created between ((d_date::date + '11:59:00'::time) at time zone '+05:00') and ((d_date::date + '18:00:00'::time) at time zone '+05:00')

当前问题：该查询返回空结果集（无报错），但确认存在匹配数据。

以下是函数的简化完整代码：

CREATE or replace FUNCTION f_loop_in_lockstep_final(_id_arr int[], _counter_arr int[], d_date date)
  RETURNS TABLE (uc_name_ varchar)
  LANGUAGE plpgsql AS  
$func$
DECLARE
   _id int;
   _counter int;
  d_date date; 
BEGIN
   FOR _id, _counter IN 
      SELECT *
      FROM   unnest (_id_arr, _counter_arr) t  
   LOOP

   RETURN QUERY
with orig_dataset as (
select routes
from campaign_routes cr  
where cr.created_at between ((d_date::date + '11:59:00'::time) at time zone '+05:00') and ((d_date::date + '18:00:00'::time) at time zone '+05:00')
)
-- 后续还有几个CTE，最终生成名为final_cte的结果集
select * from final_cte;
END LOOP;
END
$func$;

核心问题排查 #

• 变量遮蔽：函数参数已定义d_date date，但DECLARE块又重复声明同名变量，导致传入的日期参数被未初始化的局部变量（默认NULL）覆盖，WHERE子句的日期范围实际为NULL，必然返回空结果。
• 时区逻辑验证：修复变量问题后，需确认cr.created_at的时区属性与转换后的范围是否匹配——若cr.created_at是带时区的timestamp，或不带时区的本地时间，要保证时区转换后的区间能覆盖目标数据的时间。
• 循环冗余性：当前循环遍历_id_arr和_counter_arr，但查询未使用这两个变量，要么是逻辑冗余，要么是遗漏了变量关联，导致查询结果与输入参数无关。

内容的提问来源于stack exchange，提问作者analyst92