pg_dbms_job

pig build pkg pg_dbms_job;		# build deb

pig repo add pgsql -u   # add both repo and update cache

pig install pg_dbms_job;		# install via package name, for the active PG version

pig install pg_dbms_job -v 18;   # install for PG 18
pig install pg_dbms_job -v 17;   # install for PG 17
pig install pg_dbms_job -v 16;   # install for PG 16
pig install pg_dbms_job -v 15;   # install for PG 15
pig install pg_dbms_job -v 14;   # install for PG 14

shared_preload_libraries = 'pg_dbms_job';

CREATE EXTENSION pg_dbms_job;

CREATE EXTENSION pg_dbms_job;

pg_dbms_job -c /etc/pg_dbms_job/mydb-dbms_job.conf

BEGIN;
-- Scheduled job: run a procedure every day
CALL dbms_job.submit(
    job       => jobid,
    what      => 'CALL my_procedure();',
    next_date => current_timestamp + interval '1 minute',
    interval  => 'current_timestamp + ''1 day''::interval'
);
COMMIT;

BEGIN;
CALL dbms_job.broken(12345, true);   -- disable job
CALL dbms_job.broken(12345, false);  -- re-enable job
COMMIT;

BEGIN;
CALL dbms_job.change(12345, null, null, 'current_timestamp + ''3 days''::interval');
COMMIT;

BEGIN;
CALL dbms_job.interval(12345, 'current_timestamp + ''1 hour''::interval');
COMMIT;

BEGIN;
CALL dbms_job.next_date(12345, current_timestamp + interval '30 minutes');
COMMIT;

BEGIN;
CALL dbms_job.what(12345, 'CALL new_procedure();');
COMMIT;

BEGIN;
CALL dbms_job.remove(12345);
COMMIT;

CALL dbms_job.run(12345);

SELECT * FROM dbms_job.all_jobs;

SELECT * FROM dbms_job.all_scheduler_job_run_details;