Postgresql - 检测更改并调用 Web 服务

Postgresql - 检测更改并调用 Web 服务

sql
CREATE OR REPLACE FUNCTION notify_web_service() RETURNS TRIGGER AS $$
DECLARE
    url text;
    payload json;
BEGIN
    -- 设置Web服务的URL和负载数据
    url := 'https://example.com/webhook';
    payload := json_build_object('table_name', TG_TABLE_NAME, 'action', TG_OP, 'new_data', row_to_json(NEW));
    -- 调用Web服务
    PERFORM pg_notify('web_service_notification', json_build_object('url', url, 'payload', payload)::text);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER detect_change_trigger
AFTER INSERT OR UPDATE OR DELETE ON your_table
FOR EACH ROW
EXECUTE FUNCTION notify_web_service();

sql
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    amount NUMERIC(10, 2)
);
CREATE OR REPLACE FUNCTION notify_email_service() RETURNS TRIGGER AS $$
DECLARE
    email TEXT;
    subject TEXT;
    body TEXT;
BEGIN
    -- 获取订单信息
    SELECT 'customer@example.com', 'New Order Created', 'A new order with ID ' || NEW.order_id || ' has been created.' INTO email, subject, body;
    -- 发送邮件通知
    -- 实现发送邮件的代码
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER detect_order_creation_trigger
AFTER INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION notify_email_service();