Post message from SQL to a Slack channel using incoming webhook

Question

I want to send messages based upon query on Exact Online to a Slack channel using the Slack incoming webhooks and Invantive SQL.

How do I this without massive SQL functions to properly escape the JSON?

Answer 1

After some tries, I found this to be working fine:

select to_char
       ( httppost
         ( 'https://hooks.slack.com/services/XXX/YYY/zzzzzzzzz'
         , 'application/json'
         , to_binary
           ( '{'
             || jsonencode('channel')
             || ': '
             || jsonencode('#test')
             || ', '
             || jsonencode('username')
             || ': '
             || jsonencode('testuser')
             || ', '
             || jsonencode('text')
             || ': '
             || jsonencode('Companies in city of ' || act.city || ': ' || act.companynames)
             || '}'
           )
         )
       )
from   ( select act.city
         ,      listagg(act.name) companynames
         from   exactonlinerest..accounts act
         where  act.city in ( 'Haelen', 'Horn', 'Heythuysen')
         group 
         by     act.city
       )

The HTTP POST is not very elegant in the sense that it has a side effect, but it does the job.