Postgresql functions as a backend (p.3: Send SMS from the function)

Yes, Python! Getting rid of Django doesn’t mean we don’t love python anymore…

Remember to execute this function as separate call (session) to the DB. It will run as separate thread, so in case gateway doesnt work — the DB performance will not be affected.
Alternatively you could use LISTEN/NOTIFY but this is not an alternative I’m willing to explore.

I’m using http://gatewayapi.com/ here, but I guess any other will do

Prerequisites (on teh DB server of course)

pip3 install requests
pip3 install requests-oauthlib

And the function (add parallel safe just in case. In fact, we really really want to use separate worker process for this)

create or replace function sm_send_sms(phone text, msg text)
returns bool as $W$
from requests_oauthlib import OAuth1Session
import sys
phone=args[0]
msg=args[1]
key = '**<YOUR_KEY>**'
secret = '**<YOUR_SECRET>**'
gwapi = OAuth1Session(key, client_secret=secret)
req = {
    'sender': 'MYAPP',
    'message': msg,
    'recipients': [{'msisdn': phone}],
}
res = gwapi.post('https://gatewayapi.com/rest/mtsms', json=req)
res.raise_for_status()
return res
  $W$ language plpython3u parallel safe;

/* EXECUTE
select sm_send_sms('357666666','Buy cool stuff!');
*/