Postgresql trigger notification

Question

Please help me to correct sql statement:

CREATE TRIGGER transaction_insert
    AFTER INSERT ON transaction
    FOR EACH ROW
    WHEN (NEW.tr_type = 55)
    EXECUTE PROCEDURE pg_notify('check_close', to_char(NEW.n_transaction, 999999));

I need to receive notification when row with tr_type=55 is added. tr_type is int. n_transaction is int.

Postgresql 8.3.4

Please help!

Please note that "tramsaction" is a reserved word. Naming a table "transaction" is a bad choice. — wildplasser
– wildplasser, Commented May 20, 2012 at 21:08

BluesRockAddict · Accepted Answer · 2012-05-18 19:04:28Z

2

Try the following:

CREATE TRIGGER transaction_insert
    AFTER INSERT ON transaction
    FOR EACH ROW EXECUTE PROCEDURE transaction_insert_check_close();


CREATE OR REPLACE FUNCTION transaction_insert_check_close() RETURNS TRIGGER AS $transaction_insert_check_close$
BEGIN
    IF (NEW.tr_type = 55) THEN
        PERFORM pg_notify('check_close', to_char(NEW.n_transaction, 999999));
    END IF;

    RETURN NEW;
END;

$transaction_insert_check_close$ LANGUAGE plpgsql;

answered May 18, 2012 at 19:04

BluesRockAddict

15.6k3 gold badges40 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql trigger notification

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related