Is it possible to set up posgresql cron job to invoke supabsae edge function?

Question

I'm trying to set up CRON job using postresql with supabase edge function. Is it at all possible? This edge function is inserting data to a table and it works properly when invoked using curl. I found out that there is http extension to use GET or POST for posgresql. I'm receiving this error when using this:

ERROR:  function http_post(unknown) does not exist
LINE 4:       http_post(
              ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

this is mine posgresql function:

select
  cron.schedule(
    'api-test-insert',
    '15 * * * *',
    $$
    select status
    from
      http_post(
       '<supabase edge function endpoint URL>'
      )
    $$
  );

Appreciate any help!

I tried to use POST and GET to achive result. I checked GET method with fake API in posgresql cron job and it returned success with one row from API as expected. I tried GET and POST for edge function and received forementioned error result.

thorwebdev · Answer 1 · 2023-01-17T06:58:45.617

You can do this by combining pg_net with pg_cron:

select
  cron.schedule(
    'invoke-function-every-minute',
    '* * * * *', -- every minute
    $$
    select
      net.http_post(
          url:='https://project-ref.functions.supabase.co/function-name',
          headers:='{"Content-Type": "application/json", "Authorization": "Bearer YOUR_ANON_KEY"}'::jsonb,
          body:=concat('{"time": "', now(), '"}')::jsonb
      ) as request_id;
    $$
  );

Is it possible to set up posgresql cron job to invoke supabsae edge function?

1 Answers1