Supabase Postgres API Triggered Function Returns NULL result for Subqueries

Question

I have created a trigger on a table. When the trigger is activated, the trigger will call a process to pull down JSON data from Salesforce. Salesforce periodically expires their API key and I have to pull in a new API key during the callout.

The problem I am experiencing is when the trigger fires and passes the required information to the function that calls sales force, the query to get the updated API key from another table/process is coming up as NULL and the call out fails.

I do not know how to debug this issue properly to get more information nor do I know how to fix it. It is strange that it works and pulls the key when I run the process separately in the console but when the trigger processes it, the results for the query to get the API key show as NULL. Perhaps there is some limitation with the transaction processing or this needs to be run asynchronously or something. Any help would be appreciated! Here is the code:

POSTGRES V 15.1 running on Supabase

*****************TRIGGER*****************************

CREATE TRIGGER api_request_sf_trigger
    AFTER INSERT
    ON public.api_request
    FOR EACH ROW
    EXECUTE FUNCTION public.api_request_sf_trigger_func();

*****************TRIGGER FUNCTION********************

CREATE OR REPLACE FUNCTION public.api_request_sf_trigger_func()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
  
Declare
  new_type text=new.type;
  new_id text=new.foreign_id;
  sf_version text='57.0';
  
BEGIN
  RAISE LOG    'Initial trigger new.type is %', new.type;
  RAISE LOG    'Initial triggernew.foreign_id is %', new.foreign_id;
  EXECUTE public.get_sf_api((select format('%s',sf_version)), (select format('%s',new_type)) ,(select format('%s',new_id)));
Return New;

END;
$BODY$;

***********************FUNCTION CALLED BY TRIGGER******* BELOW**************

CREATE OR REPLACE FUNCTION public.get_sf_api(
sf_version text,
sobject text,
sf_foreignid text)
RETURNS void
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$

declare 
sf_record jsonb;  
sf_lastupdate timestamp;
url text;
vapi_key text;
key_header text;

begin

--ERROR AREA:  CODE TO GETS API KEY WORKS WHEN THIS FUNCTION IS EXECUTED SEPRARETLY IN PGADMIN     BUT RETURNS NULL WHEN RUN FROM TRIGGER

--OPT1 HARD CODE API KEY WORKS WITH TRIGGER
--vapi_key='HARD CODED API KEY';

--OPT2 WORKS IN CONSOLE BUT NOT IN TRIGGER BECAUSE IT RETURNS NULL(get key from other process)
--vapi_key=(Select public.get_sf_token());--refreshes API key in a separate process.

--OPT3 WORKS IN CONSOLE BUT NOT IN TRIGGER BECAUSE IT RETURNS NULL (get key from config table)
--vapi_key=(select api_key from api_info where system='Salesforce' and type='access_token' limit     1);

--ERROR AREA ABOVE********************************************************************************

RAISE LOG 'Passed sf version is %', sf_version;
RAISE LOG 'Passed sobject is %', sobject;
RAISE LOG 'Passed sf foreign_id is %', sf_foreignid;
RAISE LOG 'API KEY is %', vapi_key;

key_header=concat('OAuth ',trim(both '"' from vapi_key));

RAISE LOG 'API Key Header is %', key_header;

url=(select Concat('https://PRIVATEURL.my.salesforce.com/services/data/v',sf_version,'/query/?  q=Select+Fields(ALL)+From+',sobject,'+Where+Id=''',(trim(both '"' from sf_foreignId)),'''+Limit+1')); 

RAISE LOG '%', url;

SELECT CONTENT::jsonb->'records'->0 as records FROM http((
      'GET',
       url,
       ARRAY[http_header('Authorization',key_header)],         
       NULL,   
       NULL
    )::http_request)

INTO sf_record;  

RAISE LOG 'Passed value from other function sf_record is %', sf_record;  
sf_lastupdate=sf_record::jsonb->'LastModifiedDate'; 
RAISE LOG 'Passed value from other function sf_last updated date is %', sf_lastupdate;  

Insert INTO api_data(json_body,foreign_id,object_type,foreign_updated)
                Values (sf_record::jsonb,sf_foreignid,sobject,sf_lastupdate);

end;
$BODY$;

4th EDIT*

CREATE OR REPLACE FUNCTION public.api_request_sf_trigger_func()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
  
Declare

vapi_key text;

BEGIN

vapi_key=(select api_key from api_info limit 1);

RAISE LOG '3rd Trigger vapi_key is %',vapi_key;

Return New;

END;
$BODY$;

---

The simple select statement doesn't work in the SIMPLE 4th trigger above when run through the Supabase API. It works and pulls the API key in the select statement when I make the insert in the console but not when I run it through the API.

Answer 1

This ended up being just a permissions issue on the table with the API data. It isn't obvious but check on the Supabase permissions if you are getting NULL values back.