-1

Could someone tells me what is wrong please. I try to create a job with using PgAgent with declaring some variables. When I run this code manually, it works successfully. But when I try to put this code in job step and save it, it throws me an error.

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 $$;
AlexxxeyS
  • 9
  • 2

2 Answers2

0

It seems like PgAgent translates your code to another format, perhaps to string or something else and then can't parse it. To understand this try to:

  1. Delete some special symbols from your code like brackets, quotes etc
  2. Try to understand is it error from pgAgent or PostgreSQL Good lucK!
CottonHead
  • 1
0

There is one trick. Remove double dollar symbol before you push a save button. Then, after step was saved successfully, edit it and bring double dollars back and save changes.

AlexxxeyS
  • 9
  • 2