pg_cron.sql

DO $$
BEGIN
   IF pg_catalog.current_database() OPERATOR(pg_catalog.<>) pg_catalog.current_setting('cron.database_name') AND pg_catalog.current_database() OPERATOR(pg_catalog.<>) 'contrib_regression' THEN
      RAISE EXCEPTION 'can only create extension in database %',
                      pg_catalog.current_setting('cron.database_name')
      USING DETAIL = 'Jobs must be scheduled from the database configured in 'OPERATOR(pg_catalog.||)
                     'cron.database_name, since the pg_cron background worker 'OPERATOR(pg_catalog.||)
                     'reads job descriptions from this database.',
            HINT = pg_catalog.format('Add cron.database_name = ''%s'' in postgresql.conf 'OPERATOR(pg_catalog.||)
                          'to use the current database.', pg_catalog.current_database());
   END IF;
END;
$$;

CREATE SCHEMA cron;
CREATE SEQUENCE cron.jobid_seq;

CREATE TABLE cron.job (
	jobid bigint primary key default pg_catalog.nextval('cron.jobid_seq'),
	schedule text not null,
	command text not null,
	nodename text not null default 'localhost',
	nodeport int not null default pg_catalog.inet_server_port(),
	database text not null default pg_catalog.current_database(),
	username text not null default current_user
);
GRANT SELECT ON cron.job TO public;
ALTER TABLE cron.job ENABLE ROW LEVEL SECURITY;
CREATE POLICY cron_job_policy ON cron.job USING (username OPERATOR(pg_catalog.=) current_user);

CREATE FUNCTION cron.schedule(schedule text, command text)
    RETURNS bigint
    LANGUAGE C STRICT
    AS 'MODULE_PATHNAME', $$cron_schedule$$;
COMMENT ON FUNCTION cron.schedule(text,text)
    IS 'schedule a pg_cron job';

CREATE FUNCTION cron.unschedule(job_id bigint)
    RETURNS bool
    LANGUAGE C STRICT
    AS 'MODULE_PATHNAME', $$cron_unschedule$$;
COMMENT ON FUNCTION cron.unschedule(bigint)
    IS 'unschedule a pg_cron job';

CREATE FUNCTION cron.job_cache_invalidate()
    RETURNS trigger
    LANGUAGE C
    AS 'MODULE_PATHNAME', $$cron_job_cache_invalidate$$;
COMMENT ON FUNCTION cron.job_cache_invalidate()
    IS 'invalidate job cache';

CREATE TRIGGER cron_job_cache_invalidate
    AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE
    ON cron.job
    FOR STATEMENT EXECUTE PROCEDURE cron.job_cache_invalidate();