CODEWITHSUNDEEP
postgresqltimescaledb
Ñhosko
Ñhosko

Reputation: 795

TimescaleDB add_job not working when triggered from a function

Executing TimescaleDB add_job from a query works. For example:

SELECT add_job('my_stored_procedure', '1 day', NULL, NULL, true, NULL, true, NULL);

This returns the job_id. And I can see the job on the timescaledb_information.jobs table.

Deleting the job works, too.

SELECT delete_job(1023);

Now I'm trying to exexute the add_job function inside a function, but it fails.

Here the function (I've removed the irrelevant parts):

CREATE OR REPLACE FUNCTION check_and_add_job(proc_name_in TEXT, schedule_interval_in TEXT) RETURNS VOID AS
$$
BEGIN
  -- Here is the logic for checking if job exists, will RETURN in case it does
  -- This part will run only when there are no jobs with the same name
    PERFORM public.add_job(
        proc_name_in::text,
        schedule_interval_in::interval,
        NULL,
        NULL,
        true,
        NULL,
        true,
        NULL
    );
    RAISE NOTICE 'New job added successfully.';
    RETURN;
END;
$$ LANGUAGE plpgsql;

I'm executing the function as follows:

SELECT check_and_add_job('my_stored_procedure', '1 day');

But I get the error:

ERROR:  function public.add_job(text, interval, unknown, unknown, boolean, unknown, boolean, unknown) does not exist
LINE 1: SELECT public.add_job(proc_name_in::text, schedule_interval_...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT public.add_job(proc_name_in::text, schedule_interval_in::interval, NULL, NULL, true, NULL, true, NULL)
CONTEXT:  PL/pgSQL function check_and_add_job(text,text) line 29 at PERFORM 

SQL state: 42883

How can I perform the add_job from a function?

Upvotes: 0

Views: 186

Answers (1)

alejandrodnm
alejandrodnm

Reputation: 5640

The first argument of add_job is regproc not text:

https://docs.timescale.com/api/latest/actions/add_job/#add_job

pg:adn@localhost/target=> \df add_job
                                                                                                        List of functions
 Schema |  Name   | Result data type |                                                                              Argument data types                                                                               |   Type
--------+---------+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------
 public | add_job | integer          | proc regproc, schedule_interval interval, config jsonb, initial_start timestamp with time zone, scheduled boolean, check_config regproc, fixed_schedule boolean, timezone text | FUNCTION

Change proc_name_in::text to proc_name_in::regproc:

CREATE OR REPLACE FUNCTION check_and_add_job(proc_name_in TEXT, schedule_interval_in TEXT) RETURNS VOID AS
$$
BEGIN
  -- Here is the logic for checking if job exists, will RETURN in case it does
  -- This part will run only when there are no jobs with the same name
    PERFORM public.add_job(
        proc_name_in::regproc,
        schedule_interval_in::interval,
        NULL,
        NULL,
        true,
        NULL,
        true,
        NULL
    );
    RAISE NOTICE 'New job added successfully.';
    RETURN;
END;
$$ LANGUAGE plpgsql;```

Upvotes: 0

Related Questions