-
Notifications
You must be signed in to change notification settings - Fork 15
/
Copy path02C_send_email_sendinblue.sql
77 lines (72 loc) · 2.65 KB
/
02C_send_email_sendinblue.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
CREATE OR REPLACE FUNCTION public.send_email_sendinblue (message JSONB)
RETURNS json
LANGUAGE plpgsql
SECURITY DEFINER -- required in order to read keys in the private schema
-- Set a secure search_path: trusted schema(s), then 'pg_temp'.
-- SET search_path = admin, pg_temp;
AS $$
DECLARE
retval json;
SENDINBLUE_API_KEY text;
BEGIN
SELECT value::text INTO SENDINBLUE_API_KEY FROM private.keys WHERE key = 'SENDINBLUE_API_KEY';
IF NOT found THEN RAISE 'missing entry in private.keys: SENDINBLUE_API_KEY'; END IF;
/*
curl --request POST \
--url https://api.sendinblue.com/v3/smtp/email \
--header 'accept: application/json' \
--header 'api-key:YOUR_API_KEY' \
--header 'content-type: application/json' \
--data '{
"sender":{
"name":"Sender Alex",
"email":"[email protected]"
},
"to":[
{
"email":"[email protected]",
"name":"John Doe"
}
],
"subject":"Hello world",
"htmlContent":"<html><head></head><body><p>Hello,</p>This is my first transactional email sent from Sendinblue.</p></body></html>"
}'
*/
SELECT
* INTO retval
FROM
http
((
'POST',
'https://api.sendinblue.com/v3/smtp/email',
ARRAY[http_header ('api-key', SENDINBLUE_API_KEY)],
'application/json',
json_build_object(
'sender', json_build_object('name', message->>'sender', 'email', message->>'sender'),
'to',
json_build_array(
json_build_object('name', message->>'receipient', 'email', message->>'recipient')
),
'subject', message->>'subject',
'htmlContent', message->>'html_body',
'textConent', message->>'text_body',
'tags', json_build_array(
message->>'messageid'
)
)::text
));
-- if the message table exists,
-- and the response from the mail server contains an id
-- and the message from the mail server starts wtih 'Queued'
-- mark this message as 'queued' in our message table, otherwise leave it as 'ready'
IF (SELECT to_regclass('public.messages')) IS NOT NULL AND
retval::text = '201' THEN
UPDATE public.messages SET status = 'queued' WHERE id = (message->>'messageid')::UUID;
ELSE
RAISE 'error sending message with sendinblue: %',retval;
END IF;
RETURN retval;
END;
$$;
-- Do not allow this function to be called by public users (or called at all from the client)
REVOKE EXECUTE on function public.send_email_sendinblue FROM PUBLIC;