Hello, If i use triggers to call external API, wil...
# helpt
theuknowner
04/05/2022, 7:35 PMHello, If i use triggers to call external API, will they block IO until it is completed or are they running in background?
n
Needle
04/05/2022, 7:35 PMHello @theuknowner!
This thread has been automatically created from your message in #843999948717555735 a ``few seconds ago``.
Pinging @User so that they see this as well!
Want to unsubscribe from this thread?
Right-click the thread in Discord (or use the ... menu) and select Leave Thread to unsubscribe from future updates.
Want to change the title?
Use the
/titleg
garyaustin
04/05/2022, 8:55 PMThe Supabase function hooks are async. If you call a trigger function and use http extension it blocks…your request must return. You can probably use Supabase pg_net extension in a trigger function but not sure what state it is in, besides being used for hooks.
n
Needle
04/05/2022, 8:55 PMtheuknowner (2022-04-05)
t
theuknowner
04/05/2022, 10:23 PM@User I try to find ways in order to send push notifications to mobs when a new message has been added. Are there docs/examples for pg_net?
g
garyaustin
04/05/2022, 11:07 PMt
theuknowner
04/05/2022, 11:34 PMI created the trigger and works as expected, but I don't know how can I include data to send from the changed row. Is there any example? I checked. the docs
theuknowner
04/05/2022, 11:35 PMI have set the function to trigger when a new message has been added to table "messages". I want to get the body of the message and include it to the request
g
garyaustin
04/05/2022, 11:53 PMIn your trigger function the changed data is in "new". So new.id, new.body, etc. You would put those into the body as json pairs...
body:='{"hello": "world"}'::jsonb
t
theuknowner
04/05/2022, 11:58 PMI tried it
theuknowner
04/05/2022, 11:58 PMbegin
perform
net.http_post(
url:='https://httpbin.org/post',
body:='{"lastMes": "'+NEW.last_message_body+'" ,"hello": "world"}'::jsonb
) as request_id;
RETURN NULL;
end;
theuknowner
04/05/2022, 11:58 PMthis is my trigger function
theuknowner
04/05/2022, 11:58 PMbut I get this error -> No operator matches the given name and argument types. You might need to add explicit type casts.
theuknowner
04/05/2022, 11:59 PMoperator does not exist: unknown + text
theuknowner
04/06/2022, 12:01 AMIs "+" symbol used in Postgres Functions?
g
garyaustin
04/06/2022, 12:08 AMYeah you need to use proper postgres sql or plpgsql syntax. There are was to do formatted text and concatenate. I would have to google examples, so will leave that to you..
I have one function handy where I did in plpgsql:
Copy code
declare
myurl varchar := 'https://zkxxxxxxxxxxxxlxrq.supabase.co';
url varchar := myurl||'/storage/v1/object/'||bucket||'/'||pathname;t
theuknowner
04/06/2022, 12:09 AMI use plpgsql because I can't create trigger with sql
g
garyaustin
04/06/2022, 12:10 AMHey I'm traveling and using ipad, so hopefully got you moving right way. There are lots of examples of formatting strings out there.
t
theuknowner
04/06/2022, 12:11 AMtried with || -> body:='{"lastMes":'||NEW.last_message_body||',"hello": "world"}'::jsonb
theuknowner
04/06/2022, 12:11 AMI get -> Expected JSON value, but found \",\". . Invalid syntax json
theuknowner
04/06/2022, 12:12 AMSo where is the issue? I don't understand
g
garyaustin
04/06/2022, 12:14 AMyou need at least
Copy code
'"body":"'||NEW.last_message_body||'","hello...garyaustin
04/06/2022, 12:15 AMI'm sure there are better ways to do this formatting.
t
theuknowner
04/06/2022, 12:16 AMYes tried it, now I get "Token \"hello\" is invalid."
g
garyaustin
04/06/2022, 12:33 AMYou might want to just use sql editor and test your formatting so you can see results, or you can use
Copy code
raise log 'function message' || vargaryaustin
04/06/2022, 12:34 AMYou might want to start a new question in the SQL section on what you are trying to format as you are past the question here, and not likely to get other eyes on it.
t
theuknowner
04/06/2022, 12:38 AMOkay I'll ask in the SQL section. thanks for your time