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 your action with the job scheduler

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

  1. Write the commands you want your job to run.
    BEGIN
        RAISE NOTICE 'Executing job % with config %', job_id, config;
    END
  2. 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

  1. Add a job for your user-defined action.
    SELECT add_job('user_defined_action', '1h', config => '{"hypertable":"metr"}');
  2. The add_job call returns a job_id. It stores the job_id and config in the TimescaleDB catalog.
  3. 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

Related Content