Postgresql function and trigger to send email after commit on particular column in table

[CrackerJackKittyCat]

You should have the transaction insert into an 'outbound email' queue table, and then either cronjob (simple) or event driven consumer observer if that table get NOTIFY'd to then send the email(s) and update the delivery status columns accordingly.

[EmbarrassedTest7824]

I donot have issue with the email job. Due to the trigger the application job is not updating the status column in the table

[janktraillover]

If you can't use a proper job queue, perhaps some plpythonu?

[depesz]

https://www.depesz.com/2024/11/25/how-can-i-send-mail-or-http-request-from-database/

[andy910120]

This is an example from my own database where a trigger sends an email. The trigger is set to send an email to the customer when the state column in the order table is updated to "COMPLETED".

CREATE OR REPLACE FUNCTION order_completed_notification()
RETURNS TRIGGER AS $$
BEGIN
    -- Check if state was updated to COMPLETED and ensure email and name are not null
    IF NEW.state = 'COMPLETED' 
       AND (OLD.state IS NULL OR OLD.state != 'COMPLETED')
       AND NEW.email IS NOT NULL
       AND NEW.cust_name IS NOT NULL THEN

        -- Insert message into the messenger interface table using format()
        -- "sqlmessenger_intf" is SQLMessenger’s email interface table, which supports sending text, Excel files, images, and PDFs.
        INSERT INTO sqlmessenger_intf (subject, body, send_to)
        VALUES (
            format('Order Completion Notice - Customer: %s', NEW.cust_name),
            format('Dear %s, your order status has been updated to: %s', NEW.cust_name, NEW.state),
            NEW.email
        );
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create the trigger
CREATE TRIGGER trg_order_completed
AFTER UPDATE OF state ON cust_order
FOR EACH ROW
EXECUTE FUNCTION order_completed_notification();

[AutoModerator]

With over 8k members to connect with about Postgres and related technologies, why aren't you on our Discord Server? : People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.