Job creation and scheduling programmatically with pgagent

Question

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

I suspect there may be a way to do this by writing a postgres client using libpq (in case pgagent does not support this behavior out of the box), but I'm not sure how to do it - if I need to go down the rough spelling of my OWN API for CRUD functionality for work / schedule.

So basically I ask two questions:

• Is there a way to create / administer jobs and schedules in pagent software?
• If not, to the above question, which parts of the pagagent code do I need to connect in order to provide my own work / schedule CRUD functions?

Answer 1

Below we will create a task 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 simply creates some SQL statements, namely. Any application that can connect to the "postgres" database and has privileges to use the pgAgent schema and tables can manage jobs and schedules for pgAgent. This is just SQL.