CODEWITHSUNDEEP
postgresqltriggersgoogle-cloud-sqlpostgresql-triggerscloudsql
lapiceroazul4
lapiceroazul4

Reputation: 44

How to create a trigger in CloudSQL PostgreSQL that sends an HTTP POST request?

I have a table in a CloudSQL PostgreSQL database, and I want to create a trigger that sends an HTTP POST request whenever a new record is inserted into the table.

I know that PostgreSQL supports the CREATE TRIGGER statement, but I haven't found a direct way to send HTTP requests from the database. I came across the pg_notify approach, which could work by integrating with Pub/Sub. However, the service I need to send the request to is serverless (e.g., Cloud Run), and it's not continuously "listening," so using Pub/Sub might not be the best fit.

Upvotes: -1

Views: 72

Answers (1)

lapiceroazul4
lapiceroazul4

Reputation: 44

So basically what I did was to use pg_notify to create a pub/sub. and send a msg every time a row was created. here is the code:

-- Drop existing trigger and function if they exist
DROP TRIGGER IF EXISTS table_insert_trigger ON your_table;
DROP FUNCTION IF EXISTS notify_insert_function;

-- Create the function to notify on insert
CREATE OR REPLACE FUNCTION notify_insert_function() RETURNS TRIGGER AS $$
BEGIN
    -- Build the JSON message with relevant data from the new record
    PERFORM pg_notify(
        'your_channel', 
        json_build_object(
            'description', 'New record inserted',
            'record_id', NEW.record_id,
            'timestamp', NEW.timestamp
        )::text
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create the trigger to call the function after an insert on the table
CREATE TRIGGER table_insert_trigger
AFTER INSERT ON your_table
FOR EACH ROW
EXECUTE FUNCTION notify_insert_function();

after doing this I had to create the listener, I set up the listener in a VM, and made the configuration so that every time the msg arrives we sent the http request. Here is the code:

import psycopg2
import select
import json
import os

# Database credentials
DB_HOST = os.getenv("DB_HOST")
DB_USER = os.getenv("DB_USER")
DB_PASSWORD = os.getenv("DB_PASSWORD")
DB_NAME = os.getenv("DB_NAME")


def listen_to_notifications():
    # Connect to the database
    conn = psycopg2.connect(
        host=DB_HOST,
        database=DB_NAME,
        user=DB_USER,
        password=DB_PASSWORD,
    )
    conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
    cursor = conn.cursor()

    try:
        # Subscribe to the notification channel created in the PostgreSQL trigger
        cursor.execute("LISTEN notification_channel;")

        # Infinite loop to listen for notifications
        while True:
            # Wait for notifications
            if select.select([conn], [], [], 5) == ([], [], []):
                print("No notifications in the last 5 seconds.")
            else:
                # Process notifications
                conn.poll()
                while conn.notifies:
                    notify = conn.notifies.pop(0)
                    print(f"Received notification: {notify.payload}")
                    process_notification(notify.payload)
    except KeyboardInterrupt:
        print("Listener stopped.")
    finally:
        # When the loop is interrupted, close the connection
        cursor.execute("UNLISTEN *;")
        cursor.close()
        conn.close()
        print("Connection closed.")

#Here we can do whatever we want with the message received.
def process_notification(payload):
  pass

Upvotes: 0

Related Questions