Show result of pgAgent step

Question

How setup pgAgent step so Result or Output show the number of rows processed on my function?

my function already return the number of rows affected, but that isnt the value show in Result.

BEGIN   
    WHILE  int_pending > 0  LOOP    
        .....  
        UPDATE table SET ....   
        GET DIAGNOSTICS int_row_count = ROW_COUNT;
        int_total = int_total + int_row_count;    
    END LOOP;

    RETURN int_total;           
END;

Answer 1

According to source code of pgagent :

output = stepConn->GetLastError();

...

rc = threadConn->ExecuteVoid(
         wxT("UPDATE pgagent.pga_jobsteplog ")
         wxT("   SET jslduration = now() - jslstart, ")
         wxT("       jslresult = ") + NumToStr(rc) + wxT(", jslstatus = '") + stepstatus + wxT("', ")
         wxT("       jsloutput = ") + threadConn->qtDbString(output) + wxT(" ")
         wxT(" WHERE jslid=") + jslid);

pgagent stores last error of of job step execution.

So, there are two ways to implement desired behaviour:

• patch source code to implement your feature, recompile, install and use this custom version

• use RAISE EXCEPTION inside plpgsql function, something like this:

  CREATE OR REPLACE FUNCTION test_output() RETURNS VOID AS $$
     DECLARE
       rows int := 25;
     BEGIN
       RAISE EXCEPTION 'Updated rows: %', rows;
     END;
  $$ LANGUAGE PLPGSQL;