creating jobs and schedules programmatically with pgagent

Question

Is there a way to create and manage jobs/schedules in pgagent programmatically, i.e. WITHOUT using pgAdmin ?

I suspect there may be a way of doing this by writing a postgres client using libpq (in case pgagent does not support this behavior out of the box) - but I am not sure how to go about it - if I need to go down the rought of writing my OWN API for job/schedule CRUD functionality.

So basically I am asking two questions:

• is there a way to create/administer jobs and schedules in pagent programmatically?
• If no, to the above question, which parts of the pagagent code do I need to hook into in order to provide my own job/schedule CRUD functionality?

Answer 1

The below will create a job that runs every minute, with a step that calls some SQL:

do $$
declare
    job_id int;
begin

    /* add a job and get its id: */
    insert into 
        pgagent.pga_job (jobjclid, jobname) 
    values 
        (1 /*1=Routine Maintenance*/, 'my job name') 
    returning 
        jobid 
    into 
        job_id;


    /* add a step to the job: */
    insert into 
        pgagent.pga_jobstep (jstjobid, jstname, jstkind, jstcode, jstdbname) 
    values 
        (
            job_id, 
            'my step name', 
            's',                    /* sql step */
            'select * from thing',  /* the sql to run */
            'mydb'                  /* the name of the database to run the step against */
        );


    /* add a schedule to the job. This one runs every minute: */
    insert into 
        pgagent.pga_schedule (jscjobid, jscname) 
    values 
        (job_id, 'my schedule name');

end $$;

Answer 2

pgAdmin just creates some SQL statements, that's it. Any application that can connect to the database "postgres" and has the privileges to use the pgAgent schema and tables, can manage the jobs and schedules for pgAgent. It's just SQL.