How to setup a cron in pg_cron to run every 90 seconds

Question

I want to a cron in database using pg_cron to update the value every 90 seconds.

i am looking at this solutions Can a cron job run every 'x' seconds but this is not correct way.

also i am looking at Cron job to run a PHP script every 90 seconds between 5AM and 10PM? but runing two crons is not a viable solution if i got with this solutions and then i have to find a way to sleep the database jon for 90 secods.

If anyone has any idea please suggest me.

I want to run a cron that will run every secods in the database and it will update the value in a certain table after querying it.

Answer 1

An example of a function that I mentioned in comment:

create table update_status(id
insert into update_status values (1, now()); integer, last_update timestamp);

CREATE OR REPLACE FUNCTION public.update_check()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
    _last_update timestamp;
    _update_togo interval;
    _update_interval interval := '20 secs ';
    _sleep_interval interval;
    _select_val varchar;
BEGIN
    select into _last_update  last_update from update_status where id = 1;
    select into _update_togo now() - _last_update;
    if _update_togo < _update_interval then
        RAISE NOTICE 'Now start %', clock_timestamp();
        RAISE NOTICE 'Interval %', _update_togo;
        select into _sleep_interval _update_interval - _update_togo;
        RAISE NOTICE 'Sleep interval %', _sleep_interval;
        perform pg_sleep_for(_sleep_interval);
        select into _select_val 'Run at';
        RAISE NOTICE '% %', _select_val,clock_timestamp();
    end if;
END;
$function$
;

update  update_status set last_update = now();

select update_check();
NOTICE:  Now start 2023-01-16 11:53:15.936465-08
NOTICE:  Interval 00:00:07.314329
NOTICE:  Sleep interval 00:00:12.685671
NOTICE:  Run at 2023-01-16 11:53:28.634306-08
 update_check 
--------------

This is a simplistic proof of concept and would need more work to deal with exceptions.