Postgresql python API webhook trigger example

pg-python-api-webhook-trigger.sql

-- Creates a function that is able to request any URL and return the response
create or replace function get_http(url text, data json) returns text
  language plpython3u as
$$
    import requests
    response = requests.get(url, verify=False, json=data)
    return response.text
$$;

-- Test-example call a webhook with data and get the response
WITH todos AS (SELECT get_http('https://jsonplaceholder.typicode.com/todos/1',
  json_build_object(
    'id', 1,
    'title', 'delectus aut autem',
    'completed', false
)
)::json as response)
SELECT
  todos.response ->> 'userId' as userId,
  todos.response ->> 'id' as id
FROM todos;

-- Creates a trigger that calls a webhook with the old and new data of the order
CREATE OR REPLACE FUNCTION notify_order_change() RETURNS TRIGGER AS
$$
BEGIN
  WITH tokens as (SELECT secret_key FROM tokens WHERE name = 'webhook')
  SELECT get_http('https://backend/webhook/entries',json_build_object(
        'id', NEW.id,
        'old_data', OLD,
        'new_data', NEW,
        'secret-key', tokens.secret_key
      )
    );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;