About user-defined actions

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

PostgreSQL functions

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

User-defined actions can be written in any language you choose. This guide uses the SQL procedural language PL/pgSQL.

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

  2. CREATE OR REPLACE FUNCTION <function_name> (arguments)
  4. RETURNS <return_datatype> AS $<variable_name>$
  6.    DECLARE
  8.       <declaration>;
  10.    BEGIN
  12.       <function_body>;
  14.       RETURN { <variable_name> | value }
  16.    END; LANGUAGE <language>;

This very simple example of a function returns the total row count of a table within a database.

Free demo dataset

This example uses the Timescale Cloud Allmilk Factory demonstration dataset. You can use this educational dataset for free by signing up to Timescale Cloud.

Try Timescale Cloud for free

Use this code to create the function:

  2. CREATE OR REPLACE FUNCTION totalRecords ()
  4. RETURNS integer AS $total$
  6. declare
  8.  total integer;
  10. BEGIN
  12.    SELECT count(*) into total FROM fill_measurements;
  14.    RETURN total;
  16. END;
  18. $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:

  2. select totalRecords();

The result looks like this:

  2. totalrecords
  4. --------------
  6.      48600500
  8. (1 row)

The job scheduler

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

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

  2. SELECT * FROM timescaledb_information.jobs;

The result looks like this:

  2. 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
  4. --------+----------------------------+-------------------+-------------+-------------+--------------+-----------------------+------------------+-----------+-----------+------------------------+-------------------------------+-------------------+-----------------
  6.       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 |                   |
  8.    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 |                   |
  10. (2 rows)

Native job-scheduling policies

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.