How to execute PostgreSQL function at specific time interval?-CodePudding

Is there anything similar to setTimeout setTimeInterval in PostgreSQL which allows to execute piece of code (FUNCTION) at specified time interval?

As far as I know only thing that can execute a FUNCTION according to certain event is Triggers but it is not time based but operation driven (INSERT / UPDATE / DELETE / TRUNCATE)

While I could do this in application code, but prefer to have it delegated to database. Anyway I could achieve this in PostgreSQL? May be an extension?

CodePudding user response：

Yes, there is a way to do this. It's called pg_sleep:

CREATE OR REPLACE FUNCTION my_function() RETURNS VOID AS $$
BEGIN
    LOOP
        PERFORM pg_sleep(1);
        RAISE NOTICE 'This is a notice!';
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT my_function();

This will raise the notice every second. You can also make it do other things instead of raising a notice.

You can use PostgreSQL's Background Worker feature.

The following is a simple example of a background worker that prints a message every 5 seconds:

CREATE OR REPLACE FUNCTION print_message() RETURNS VOID AS $$
BEGIN
  RAISE NOTICE 'Hello, world!';
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION schedule_print_message() RETURNS VOID AS $$
DECLARE
  job_id BIGINT;
BEGIN
  SELECT bgw_start_recurring_job(
    'print-message',
    'print_message',
    '5 seconds'
  ) INTO job_id;
END;
$$ LANGUAGE plpgsql;

SELECT schedule_print_message();