Create and register user-defined actions

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

Define a function or procedure

To create an action, first write the commands you want your function or procedure to execute. Then wrap your commands in a CREATE OR REPLACE statement. You can also define the language of your commands in this statement. The example below uses PLPGSQL.

The outer statement contains the action signature: (job_id INT, config JSONB). Include the signature literally. Don't replace with an actual job ID or config object. You define config when you register your job. job_id is also automatically generated then.

The following example defines a simple procedure that raises a notice. Replace the RAISE NOTICE with the commands you want to run.

Defining a procedure

Write the commands you want your job to run.

BEGIN
    RAISE NOTICE 'Executing job % with config %', job_id, config;
END

Wrap the commands in a CREATE OR REPLACE PROCEDURE statement.

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.

Registering an action

Add a job for your user-defined action.

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

The add_job call returns a job_id. It stores the job_id and config in the TimescaleDB catalog.
The action runs on schedule. 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.

List current jobs

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

SELECT * FROM timescaledb_information.jobs;

Found an issue on this page?

Report an issue!

Keywords

action TimescaleDB

PreviousAbout user-defined actions NextTest and debug a user-defined action