skip to Main Content

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 $$;

2

Answers


  1. Chosen as BEST ANSWER

    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.


  2. 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!
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search