You can create user-defined actions that periodically run custom functions or procedures on your database.

You can use PostgreSQL functions to create database operations that would typically take several queries or steps. Functions can also be used by other applications that interact with your database to perform tasks without additional code.

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

The basic syntax of a function suitable for a user-defined action is:

CREATE FUNCTION <function_name> (arguments)
RETURNS <return_datatype> AS $<variable_name>$
DECLARE
<declaration>;
BEGIN
<function_body>;
RETURN { <variable_name> | value }
END; LANGUAGE <language>;

This is an example of a function returns the total row count of a table within a database.

Use this code to create the function:

CREATE FUNCTION totalRecords (job_id INT DEFAULT NULL, config JSONB DEFAULT NULL)
RETURNS integer AS $total$
declare
total integer;
BEGIN
SELECT count(*) into total FROM fill_measurements;
RETURN total;
END;
$total$ LANGUAGE plpgsql;

When you execute this code in psql, it returns CREATE FUNCTION to indicate that it has successfully been created. You can then execute a call to the function, like this:

select totalRecords();

The result looks like this:

totalrecords
--------------
48600500
(1 row)

When you have created your function, you need to register it with the job scheduler to make the function run regularly. You can do with the add_job function. This example adds the totalRecords function, and tells it to run every hour:

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

For the config value, if you don't need any special configuration parameters, you can use NULL.

You can see a list of all your currently registered jobs by querying the job scheduler, like this:

SELECT * FROM timescaledb_information.jobs;

The result looks like this:

job_id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | config | next_start | hypertable_schema | hypertable_name
--------+----------------------------+-------------------+-------------+-------------+--------------+-----------------------+------------------+-----------+-----------+------------------------+-------------------------------+-------------------+-----------------
1 | Telemetry Reporter [1] | 24:00:00 | 00:01:40 | -1 | 01:00:00 | _timescaledb_internal | policy_telemetry | postgres | t | | 2022-08-18 06:26:39.524065+00 | |
1000 | User-Defined Action [1000] | 01:00:00 | 00:00:00 | -1 | 00:05:00 | public | totalrecords | tsdbadmin | t | {"hypertable": "metr"} | 2022-08-17 07:17:24.831698+00 | |
(2 rows)

TimescaleDB natively includes some job-scheduling policies, such as:

If these don't cover your use case, or if you want to expand upon the native policy features, you can write a user-defined action.

Keywords

Found an issue on this page?Report an issue or Edit this page in GitHub.