-
Notifications
You must be signed in to change notification settings - Fork 14
/
02D_send_email_mailjet.sql
96 lines (91 loc) · 3.35 KB
/
02D_send_email_mailjet.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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
CREATE OR REPLACE FUNCTION public.send_email_mailjet (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;
MAILJET_API_KEY text;
MAILJET_SECRET_KEY text;
BEGIN
SELECT value::text INTO MAILJET_API_KEY FROM private.keys WHERE key = 'MAILJET_API_KEY';
IF NOT found THEN RAISE 'missing entry in private.keys: MAILJET_API_KEY'; END IF;
SELECT value::text INTO MAILJET_SECRET_KEY FROM private.keys WHERE key = 'MAILJET_SECRET_KEY';
IF NOT found THEN RAISE 'missing entry in private.keys: MAILJET_SECRET_KEY'; END IF;
SELECT
* INTO retval
FROM
http
((
'POST',
'https://api.mailjet.com/v3.1/send',
ARRAY[http_header ('Authorization',
--'Basic ' || encode((MAILJET_API_KEY || ':' || MAILJET_SECRET_KEY)::bytea, 'base64'::text))],
'Basic ' || regexp_replace(encode((MAILJET_API_KEY || ':' || MAILJET_SECRET_KEY)::bytea, 'base64')::text, '\s', '', 'g')
)],
'application/json',
json_build_object(
'Messages', json_build_array(
json_build_object(
'From', json_build_object(
'Email', message->>'sender',
'Name', message->>'sender'
),
'To', json_build_array(
json_build_object(
'Email', message->>'recipient',
'Name', message->>'recipient'
)
),
'Subject', message->>'subject',
'TextPart', message->>'text_body',
'HTMLPart', message->>'html_body' --,
--'CustomID', 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 = '200' THEN
UPDATE public.messages SET status = 'queued' WHERE id = (message->>'messageid')::UUID;
ELSE
RAISE 'error sending message with mailjet: %',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_mailjet FROM PUBLIC;
/*
curl -s \
-X POST \
--user "MAILJET_API_KEY:MAILJET_SECRET_KEY" \
https://api.mailjet.com/v3.1/send \
-H 'Content-Type: application/json' \
-d '{
"Messages":[
{
"From": {
"Email": "[email protected]",
"Name": "[email protected]"
},
"To": [
{
"Email": "[email protected]",
"Name": "[email protected]"
}
],
"Subject": "My first Mailjet email",
"TextPart": "Greetings from Mailjet.",
"HTMLPart": "<h3>Dear passenger 1, welcome to <a href='https://www.mailjet.com/'>Mailjet</a>!</h3><br />May the delivery force be with you!",
"CustomID": "AppGettingStartedTest"
}
]
}'
*/