安装插件plpython3u
psql etl -U postgres
create extension plpython3u
pip安装requests
cd /opt && wget https://bootstrap.pypa.io/get-pip.py
python get-pip.py
pip install requests
sp_send
- plpython3u为不受信语言,所以只能被超级用户使用
create or replace function tool.sp_send(
message json
,webhook varchar
,secret varchar
)
returns text
language plpython3u
security definer
as $function$
import requests
import json
import time
import hmac
import hashlib
import base64
import urllib.parse
"""
/*
* 作者 : v-yuzhenc
* 功能 : 给钉钉发送一条消息
* message : 需要发送的消息,json格式,详情参考https://open.dingtalk.com/document/robots/custom-robot-access
* webhook : 钉钉机器人的webhook
* secret : 钉钉机器人的secret
* */
"""
v_timestamp = str(round(time.time() * 1000))
p_secret = secret
secret_enc = p_secret.encode('utf-8')
string_to_sign = '{}\n{}'.format(v_timestamp, p_secret)
string_to_sign_enc = string_to_sign.encode('utf-8')
hmac_code = hmac.new(secret_enc, string_to_sign_enc, digestmod=hashlib.sha256).digest()
v_sign = urllib.parse.quote_plus(base64.b64encode(hmac_code))
p_webhook = webhook
webhook_url = p_webhook+"×tamp="+v_timestamp+"&sign="+v_sign
p_message = json.loads(message)
response = requests.post(webhook_url, data=json.dumps(p_message), headers={"Content-Type": "application/json"})
return response.text
$function$;
alter function tool.sp_send(json,varchar,varchar) owner to tool;
grant execute on function tool.sp_send(json,varchar,varchar) to public;
测试
select sp_send('{
"msgtype": "actionCard",
"actionCard": {
"title": "我 20 年前想打造一间苹果咖啡厅,而它正是 Apple Store 的前身",
"text": "![screenshot](https://img.alicdn.com/tfs/TB1NwmBEL9TBuNjy1zbXXXpepXa-2400-1218.png) \n\n #### 乔布斯 20 年前想打造的苹果咖啡厅 \n\n Apple Store 的设计正从原来满满的科技感走向生活化,而其生活化的走向其实可以追溯到 20 年前苹果一个建立咖啡馆的计划",
"btnOrientation": "0",
"btns": [
{
"title": "内容不错",
"actionURL": "https://www.dingtalk.com/"
},
{
"title": "不感兴趣",
"actionURL": "https://www.dingtalk.com/"
}
]
}
}'::json);
参考
自定义机器人安全设置 - 钉钉开放平台
自定义机器人接入 - 钉钉开放平台