Cannot call supabase_functions.http_request inside another function

[Eldynn]

I'm trying to do a function that wrap supabase_functions.http_request so I can call the correct function url depending on the environment it run onto:

CREATE SCHEMA IF NOT EXISTS not_public;

select vault.create_secret('SUPABASE_URL', 'http://supabase_kong_back-office:8000');

CREATE FUNCTION not_public.process_database_webhooks()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'supabase_functions'
AS $function$
DECLARE
  url text; 
BEGIN
  select name || '/functions/v1/database_webhooks'
  into url
  from vault.decrypted_secrets
  where name = 'SUPABASE_URL';

  return supabase_functions.http_request(
    url,
    'POST',
    '{ "Content-type":"application/json", "Authorization": "xxxxx" }',
    '{}',
    '1000'
  );
END;
$function$
;

CREATE TRIGGER each_row_inserted_on_talent_that_does_not_work
AFTER UPDATE ON public."Talent"
FOR EACH ROW EXECUTE FUNCTION
not_public.process_database_webhooks();

The issue I get is that the error:

function supabase_functions.http_request(text, unknown, unknown, unknown, unknown) does not exist

Keep hitting me each time I try to use supabase_functions.http_request outside the classic way:

CREATE TRIGGER each_row_inserted_on_talent_that_work
AFTER UPDATE ON public."Talent"
FOR EACH ROW EXECUTE FUNCTION
supabase_functions.http_request('http://supabase_kong_back-office:8000/functions/v1/database_webhooks', 'POST', '{"Content-type":"application/json","Authorization":"Bearer xxxxx"}', '{}', '1000');

I've try (without success):

• to use SECURITY DEFINER
• to SET search_path TO 'supabase_functions'
• to cast every arguments explicitly ('{}'::jsonb, ...)
• and some other tweaks

I'm gonna copy the declaration of supabase_functions.http_request and create my own function, but I wonder if it is possible?

[GaryAustin1]

Where did supabase_functions come from? Normally http extension is installed in extensions.

[Eldynn]

When I create via the UI a database webhook, and then use the db diff via the CLI it use the supabase function from this schema not directly pg_net.

[GaryAustin1]

I missed this was pg_net versus http extension.  Here I use both https://github.com/GaryAustin1/supa-file-helper/blob/main/main.sqlAnd use net.http_request even though it is installed in extensions.  Sent from my iPhoneOn Jun 27, 2023, at 9:53 AM, Robin Baillargeaux ***@***.***> wrote: When I create via the UI a database webhook, and then use the db diff via the CLI it use the supabase function from this schema not directly pg_net. —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you commented.Message ID: ***@***.***>

[Eldynn]

ended up creating my own function like the one in supabase_functions:

CREATE SCHEMA IF NOT EXISTS not_public;

CREATE OR REPLACE FUNCTION not_public.base_url()
 RETURNS text
 LANGUAGE plpgsql
AS $function$
  DECLARE
    base_url text;
  BEGIN
    select decrypted_secret
    into base_url
    from vault.decrypted_secrets
    where name = 'SUPABASE_URL';

    IF base_url IS NULL THEN
      RAISE EXCEPTION 'base_url is not configured?';
    END IF;

    return base_url;
  END
$function$;

CREATE OR REPLACE FUNCTION not_public.functions_default_headers()
 RETURNS jsonb
 LANGUAGE plpgsql
AS $function$
  DECLARE
    supabase_anon_key text;
    headers jsonb;
  BEGIN
    select decrypted_secret
    into supabase_anon_key
    from vault.decrypted_secrets
    where name = 'SUPABASE_ANON_KEY';

    IF supabase_anon_key IS NULL THEN
      RAISE EXCEPTION 'supabase_anon_key is not configured?';
    END IF;

    headers = jsonb_build_object(
      'Content-Type', 'application/json',
      'Authorization', 'Bearer ' || supabase_anon_key
    );

    return headers;
  END
$function$;

CREATE OR REPLACE FUNCTION not_public.http_request()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'supabase_functions'
AS $function$
  DECLARE
    request_id bigint;
    payload jsonb;
    path text := TG_ARGV[0]::text;
    method text := TG_ARGV[1]::text;
    headers jsonb DEFAULT NULL;
    params jsonb DEFAULT '{}'::jsonb;
    timeout_ms integer DEFAULT 1000;
  BEGIN
    IF path IS NULL OR path = 'null' THEN
      RAISE EXCEPTION 'path argument is missing';
    END IF;

    IF method IS NULL OR method = 'null' THEN
      RAISE EXCEPTION 'method argument is missing';
    END IF;

    IF TG_ARGV[2] IS NULL OR TG_ARGV[2] = 'null' THEN
      headers = not_public.functions_default_headers();
    ELSE
      RAISE EXCEPTION 'custom headers argument is not implemented';
    END IF;

    IF TG_ARGV[3] IS NULL OR TG_ARGV[3] = 'null' THEN
      params = '{}'::jsonb;
    ELSE
      params = TG_ARGV[3]::jsonb;
    END IF;

    IF TG_ARGV[4] IS NULL OR TG_ARGV[4] = 'null' THEN
      timeout_ms = 1000;
    ELSE
      timeout_ms = TG_ARGV[4]::integer;
    END IF;
    
    CASE
      WHEN method = 'GET' THEN
        SELECT http_get INTO request_id FROM net.http_get(
          not_public.base_url() || path,
          params,
          headers,
          timeout_ms
        );
      WHEN method = 'POST' THEN
        payload = jsonb_build_object(
          'old_record', OLD,
          'record', NEW,
          'type', TG_OP,
          'table', TG_TABLE_NAME,
          'schema', TG_TABLE_SCHEMA
        );

        SELECT http_post INTO request_id FROM net.http_post(
          not_public.base_url() || path,
          payload,
          params,
          headers,
          timeout_ms
        );
      ELSE
        RAISE EXCEPTION 'method argument % is invalid', method;
    END CASE;

    INSERT INTO supabase_functions.hooks
      (hook_table_id, hook_name, request_id)
    VALUES
      (TG_RELID, TG_NAME, request_id);

    RETURN NEW;
  END
$function$;

CREATE TRIGGER each_row_inserted_on_talent
AFTER UPDATE ON public."Talent"
FOR EACH ROW EXECUTE FUNCTION
not_public.http_request('/functions/v1/database_webhooks', 'POST');

[tecoad]

you saved my day.
Do you still using this method?

[Eldynn]

Sorry I didn't see the notification. We're still using it since then.

[matiasbattocchia]

My guess is that supabase_functions.http_request is not a normal function but a trigger function, that is why it cannot be called from anywhere but trigger clauses.

[Eldynn]

It is but the strange thing is that I was trying to call it from a trigger...