PgAgent作业步骤添加含DECLARE的PL/pgSQL代码报错求助

问题描述 #

手动执行正常的PL/pgSQL DO匿名块，添加到PgAgent作业步骤保存时触发错误：syntax error at or near DECLARE。

原因分析 #

1. HTML转义字符干扰：你提供的代码里包含<、>、"这类HTML转义字符，PgAgent会把它们当作普通SQL字符处理，直接导致语法错误。
2. PgAgent执行语言不匹配：PgAgent作业步骤默认执行语言可能是sql，而DO块属于PL/pgSQL语法，普通SQL模式无法识别DECLARE关键字。

解决方案 #

方案一：清理代码并指定执行语言 #

1. 先把代码中的HTML转义字符替换为原生SQL符号：
   • < → <
   • > → >
   • " → "
     修正后的代码如下：

   DO $$ 
   DECLARE
       start_date date;
       dates date;
       d SMALLINT;
       counter integer := 0;
       res date[];
       treshold bigint;
   BEGIN
       TRUNCATE ditdemo.daily;
       start_date:= now();
       dates := start_date;
       while counter <= 14 loop
           dates := dates - INTERVAL '1 DAY';
           select cal.is_holiday into d from ditdemo.calendar as cal where cal.calendardate = dates;
           if d=0 then
              res := array_append(res,dates);
              counter := counter + 1;
           end if;
   /*       
           raise notice 'dates %', dates;
           raise notice 'is holiday %', d;
           raise notice 'result %', res;
   */
       end loop;
       
       insert into ditdemo.daily
       select
       time_bucket('1 day', j."timestamp") as day,
       j.account,
       count(*) as cnt
       from ditdemo.jrnl as j
       where
           cast(j."timestamp" as date) in (select unnest(res)) AND
           j.account not in (select account from ditdemo.user where is_service = 1)
       group by day, j.account;
       
       SELECT
       round(PERCENTILE_CONT(0.95) WITHIN GROUP(ORDER BY d.cnt))
       into treshold
       FROM ditdemo.daily as d;
       
       UPDATE ditdemo.calendar
       SET daily_treshold = treshold
       WHERE calendardate > start_date and calendardate <=(start_date::date + interval '7 day');
   
   END $$;
   

2. 在PgAgent创建作业步骤时，找到执行语言选项，选择plpgsql（而非默认的sql），再粘贴修正后的代码保存即可。

方案二：封装为存储过程（更推荐） #

把DO块逻辑封装成存储过程，后续PgAgent直接调用存储过程，避免语法识别问题：

1. 创建存储过程：

   CREATE OR REPLACE PROCEDURE ditdemo.refresh_daily_data()
   LANGUAGE plpgsql
   AS $$
   DECLARE
       start_date date;
       dates date;
       d SMALLINT;
       counter integer := 0;
       res date[];
       treshold bigint;
   BEGIN
       TRUNCATE ditdemo.daily;
       start_date:= now();
       dates := start_date;
       while counter <= 14 loop
           dates := dates - INTERVAL '1 DAY';
           select cal.is_holiday into d from ditdemo.calendar as cal where cal.calendardate = dates;
           if d=0 then
              res := array_append(res,dates);
              counter := counter + 1;
           end if;
       end loop;
       
       insert into ditdemo.daily
       select
       time_bucket('1 day', j."timestamp") as day,
       j.account,
       count(*) as cnt
       from ditdemo.jrnl as j
       where
           cast(j."timestamp" as date) in (select unnest(res)) AND
           j.account not in (select account from ditdemo.user where is_service = 1)
       group by day, j.account;
       
       SELECT
       round(PERCENTILE_CONT(0.95) WITHIN GROUP(ORDER BY d.cnt))
       into treshold
       FROM ditdemo.daily as d;
       
       UPDATE ditdemo.calendar
       SET daily_treshold = treshold
       WHERE calendardate > start_date and calendardate <=(start_date::date + interval '7 day');
   END $$;
   

2. 在PgAgent作业步骤中，执行以下调用语句即可：

   CALL ditdemo.refresh_daily_data();
   

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