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 ajob_id
. It stores thejob_id
andconfig
in the TimescaleDB catalog. - The action runs on schedule. It also runs when you manually start it by
calling
run_job
with thejob_id
. When it runs, thejob_id
andconfig
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