Create and register user-defined actions

Adding a user-defined action to your database is a 2-step process:

1. Define a function or procedure
2. Register the action with the job scheduler

Define a function or procedure

To create an action, begin by defining the function or procedure that you want your database to execute.

Your function needs to be wrapped in a CREATE OR REPLACE statement. This statement also allows you to define the language of your commands in this statement. User-defined actions can be written in any language you choose. This guide uses the SQL procedural language PL/pgSQL.

This example defines a simple procedure that raises a notice:

CREATE OR REPLACE PROCEDURE user_defined_action(job_id INT, config JSONB)
    LANGUAGE PLPGSQL AS
    $$
    BEGIN
        RAISE NOTICE 'Executing job % with config %', job_id, config;
    END
    $$;

Register an action

To make the job scheduler run your action, you need to register it. Use the add_job function. Supply the name of your action, the schedule you want it to run on, and the content of your config. If your job needs no parameters, use a NULL config.

For example:

SELECT add_job('user_defined_action', '1h', config => '{"hypertable":"metrics"}');

The add_job call returns a job_id. It stores the job_id and config in the TimescaleDB catalog.

The action runs on the schedule you set. It also runs when you manually start it by calling run_job with the job_id. When it runs, the job_id and config are passed as arguments to the procedure.

To list all currently registered jobs, query timescaledb_information.jobs:

SELECT * FROM timescaledb_information.jobs;