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:

2. CREATE OR REPLACE PROCEDURE user_defined_action(job_id INT, config JSONB)

4. LANGUAGE PLPGSQL AS

6. $$

8. BEGIN

10. RAISE NOTICE 'Executing job % with config %', job_id, config;

12. END

14. $$;

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:

2. 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:

2. SELECT * FROM timescaledb_information.jobs;