PostgreSQL：如何在DATE_PART中使用子函数返回值计算r_interval？

我有一个生成数据表的函数，该表后续由C#调度模块更新。现在计划把计算逻辑从C#模块迁移到这个SQL函数里，废弃C#模块。主函数会调用不可修改的子函数getintervalreading获取指定天数（比如30天）内的信息，但添加第二个DATE_PART调用计算r_interval时，因涉及子函数返回变量的语法错误导致执行失败——原函数在添加该调用前能正常运行。

执行的SQL语句：

select sub.id, 
       sub.reading_date, 
       sub.response_age, 
       (rInterval)."ReadingDate", 
       r_interval       
from (
  select d.id, 
         d.reading_date,    
         DATE_PART('day', now() - d.reading_date)::integer as response_age, 
        getintervalreading(d.id, 30) as rInterval,
        DATE_PART(‘day’, d.reading_date – (rInterval)."ReadingDate")::integer as r_interval
   from table_device d 
   where d.active = true
) as sub;

报错信息：

ERROR: syntax error at or near "–"
LINE 14: DATE_PART(‘day’, d.reading_date – (rInterval)."ReadingDat...
^

子函数getintervalreading定义：

FUNCTION public.getintervalreading(
         deviceid integer,
         intervaldays integer)
RETURNS TABLE("ReadingDate" date) 
LANGUAGE 'sql' COST 100 VOLATILE PARALLEL UNSAFE ROWS 1000

函数体：

select rInterval.reading_date
from table_reading rInterval
where rInterval.device_id = deviceId 
        and rInterval.status = 'Valid' 
        and rInterval.reading_date > (current_date - intervaldays) 
order by rInterval.reading_date asc limit 1

问题核心：能否使用子函数的返回值计算r_interval？如果可以，正确的语法或实现方法是什么？我试过多种DATE_PART中使用rInterval结果的语法变体都没成功，也查过CASE语句里用子函数结果的示例。

错误原因分析 #

1. 符号错误：报错指向的–是中文破折号，不是SQL认可的英文半角减号-，这是直接触发语法错误的原因。
2. 别名引用顺序问题：即使修正符号，在同一个SELECT子句里，刚定义的别名rInterval无法被同层级的其他表达式引用——SQL中SELECT子句的字段是并行计算的，别名还未生效。

正确实现方法 #

可以用LATERAL JOIN调用子函数，这样子函数的结果能被后续的SELECT子句直接引用，同时修正符号问题：

select 
    d.id, 
    d.reading_date,    
    DATE_PART('day', now() - d.reading_date)::integer as response_age, 
    ir."ReadingDate",
    DATE_PART('day', d.reading_date - ir."ReadingDate")::integer as r_interval
from table_device d
left join lateral getintervalreading(d.id, 30) ir on true
where d.active = true;

说明 #

• LATERAL JOIN允许在JOIN子句中调用依赖于左表（table_device）字段的函数，每一行设备数据都会触发一次getintervalreading调用，获取对应的ReadingDate。
• 用left join保证即使子函数返回空结果（比如某设备没有符合条件的读数），主表的设备数据依然会被保留；如果只需要有有效读数的设备，换成inner join即可。
• 所有符号都使用英文半角，避免语法错误。

内容的提问来源于stack exchange，提问作者G.Lowell