PL/pgSQL函数WITH AS语句语法错误排查与优化求助

问题背景 #

原本功能正常的PL/pgSQL函数，最初采用临时表+插入操作实现，改用WITH AS语句优化后执行报错，错误信息：

ERROR: syntax error at end of input LINE 48: ...r = p_id_var AND
fvr.utz BETWEEN p_utz_begin AND p_utz_end);

^ SQL state: 42601 Character: 1754

原函数代码：

CREATE OR REPLACE FUNCTION tlm.main_dash_tele_freq_blackout(
    p_id_unit integer,
    p_utz_begin timestamp without time zone,
    p_utz_end timestamp without time zone)
    RETURNS TABLE(can_freq interval, can_blackout interval, gps_freq interval, gps_blackout interval, chargeloss boolean) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
DECLARE
     CAN_freq interval;
     CAN_blackout interval;
     CAN_chargeloss boolean;
     GPS_freq interval;
     GPS_blackout interval;
     max_diff integer;
     p_id_var integer;
    
BEGIN

p_id_var = 1001;
    
with main_dash_tele_freq_blackout_first_reading as (
    SELECT fvr.utz, fvr.val FROM var.oper_readings fvr WHERE fvr.id_unit = p_id_unit AND fvr.id_var = p_id_var AND fvr.utz BETWEEN p_utz_begin AND p_utz_end
),

main_dash_tele_freq_blackout_second_reading as ( 
    SELECT fr.utz , fr.val FROM main_dash_tele_freq_blackout_first_reading fr WHERE fr.id != 1),

 main_dash_tele_freq_blackout_result_reading as (
    SELECT ff.utz, ss.utz,  (ss.utz - ff.utz), (ss.val - ff.val) FROM main_dash_tele_freq_blackout_first_reading ff FULL JOIN main_dash_tele_freq_blackout_second_reading ss ON ff.id = ss.id
)
; 

CAN_freq = (SELECT AVG(diff) FROM main_dash_tele_freq_blackout_result_reading WHERE diff < '00:10:00');

CAN_blackout = (SELECT  AVG(diff)  FROM main_dash_tele_freq_blackout_result_reading WHERE diff > '00:10:00' AND (diff_val > 1 OR diff_val < -1));

CAN_chargeloss = (SELECT  (MAX(diff_val)>10)  FROM main_dash_tele_freq_blackout_result_reading WHERE diff > '00:10:00' AND (diff_val > 1 OR diff_val < -1));

------------------------------------------------------ Similar case for this variables ------------------------------------------------

with main_dash_tele_freq_blackout_first_GPS_reading as (
    SELECT  fvr.utz, fvr.lat, fvr.lon FROM var.oper_geo_readings fvr WHERE fvr.id_unit = p_id_unit AND fvr.utz BETWEEN p_utz_begin AND p_utz_end
),

 main_dash_tele_freq_blackout_second_GPS_reading as ( 
    SELECT fr.utz , fr.lat, fr.lon FROM main_dash_tele_freq_blackout_first_GPS_reading fr WHERE fr.id != 1
),

 main_dash_tele_freq_blackout_result_GPS_reading as (
    SELECT ff.utz, ss.utz,  (ss.utz - ff.utz), (ss.lat - ff.lat), (ss.lon - ff.lon) FROM main_dash_tele_freq_blackout_first_GPS_reading ff FULL JOIN main_dash_tele_freq_blackout_second_GPS_reading ss ON ff.id = ss.id
);

GPS_freq = (SELECT AVG(diff) FROM main_dash_tele_freq_blackout_result_GPS_reading WHERE diff < '00:10:00');

GPS_blackout = (SELECT  AVG(diff)  FROM main_dash_tele_freq_blackout_result_GPS_reading WHERE diff > '00:10:00');

RETURN QUERY (SELECT CAN_freq, CAN_blackout, GPS_freq, GPS_blackout, CAN_chargeloss );

END
$BODY$;

错误原因与修正步骤 #

1. WITH子句语法错误 #

PL/pgSQL中WITH CTE不能单独定义后直接加;，必须作为后续查询的一部分，或通过SELECT ... INTO将CTE结果存入临时结构供后续使用。

2. 计算列未定义别名 #

CTE中计算的(ss.utz - ff.utz)、(ss.val - ff.val)等列没有指定别名，后续查询用diff、diff_val引用会报错，必须为这些列定义别名。

3. 引用不存在的id列 #

原代码用fr.id != 1排除第一行，但前面的SELECT未包含id列，且这种方式无法准确获取"第一行"。改用窗口函数ROW_NUMBER()按时间排序后筛选行更可靠。

修正后的函数代码 #

CREATE OR REPLACE FUNCTION tlm.main_dash_tele_freq_blackout(
    p_id_unit integer,
    p_utz_begin timestamp without time zone,
    p_utz_end timestamp without time zone)
    RETURNS TABLE(can_freq interval, can_blackout interval, gps_freq interval, gps_blackout interval, chargeloss boolean) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
DECLARE
     CAN_freq interval;
     CAN_blackout interval;
     CAN_chargeloss boolean;
     GPS_freq interval;
     GPS_blackout interval;
     max_diff integer;
     p_id_var integer;
    
BEGIN

p_id_var = 1001;

-- 处理CAN相关计算，将CTE整合到查询中
WITH main_dash_tele_freq_blackout_first_reading AS (
    SELECT 
        fvr.utz, 
        fvr.val,
        ROW_NUMBER() OVER (ORDER BY fvr.utz) AS rn  -- 添加行号用于关联下一行
    FROM var.oper_readings fvr 
    WHERE fvr.id_unit = p_id_unit 
      AND fvr.id_var = p_id_var 
      AND fvr.utz BETWEEN p_utz_begin AND p_utz_end
),
main_dash_tele_freq_blackout_result_reading AS (
    SELECT 
        ff.utz AS first_utz,
        ss.utz AS second_utz,
        (ss.utz - ff.utz) AS diff,  -- 定义别名diff
        (ss.val - ff.val) AS diff_val  -- 定义别名diff_val
    FROM main_dash_tele_freq_blackout_first_reading ff
    JOIN main_dash_tele_freq_blackout_first_reading ss 
      ON ff.rn = ss.rn - 1  -- 通过行号关联前后行，替代原错误的FULL JOIN
)
SELECT 
    AVG(CASE WHEN diff < '00:10:00' THEN diff END),
    AVG(CASE WHEN diff > '00:10:00' AND (diff_val > 1 OR diff_val < -1) THEN diff END),
    MAX(CASE WHEN diff > '00:10:00' AND (diff_val > 1 OR diff_val < -1) THEN diff_val END) > 10
INTO CAN_freq, CAN_blackout, CAN_chargeloss
FROM main_dash_tele_freq_blackout_result_reading;

-- 处理GPS相关计算
WITH main_dash_tele_freq_blackout_first_GPS_reading AS (
    SELECT 
        fvr.utz, 
        fvr.lat, 
        fvr.lon,
        ROW_NUMBER() OVER (ORDER BY fvr.utz) AS rn
    FROM var.oper_geo_readings fvr 
    WHERE fvr.id_unit = p_id_unit 
      AND fvr.utz BETWEEN p_utz_begin AND p_utz_end
),
main_dash_tele_freq_blackout_result_GPS_reading AS (
    SELECT 
        ff.utz AS first_utz,
        ss.utz AS second_utz,
        (ss.utz - ff.utz) AS diff  -- 定义别名diff
    FROM main_dash_tele_freq_blackout_first_GPS_reading ff
    JOIN main_dash_tele_freq_blackout_first_GPS_reading ss 
      ON ff.rn = ss.rn - 1
)
SELECT 
    AVG(CASE WHEN diff < '00:10:00' THEN diff END),
    AVG(CASE WHEN diff > '00:10:00' THEN diff END)
INTO GPS_freq, GPS_blackout
FROM main_dash_tele_freq_blackout_result_GPS_reading;

RETURN QUERY SELECT CAN_freq, CAN_blackout, GPS_freq, GPS_blackout, CAN_chargeloss;

END
$BODY$;

额外说明 #

• 原代码中的FULL JOIN改为JOIN，因为通过行号关联前后行时，只有连续行才有业务意义，FULL JOIN会引入大量无效NULL值。
• 用ROW_NUMBER()按时间排序后关联前后行，替代原错误的fr.id !=1，确保逻辑准确。

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