Intimation from PSQL trigger

Question

In my program, I am accessing Postgresql database.

I don't want to watch database regularly,

So When ever the specified table get changed by various actions ( insert, update, delete ), I need to get some signal or message to my program,

So I have an idea to use Triggers, But I don't know how to send signal or API or message to my program ( it can be C or perl program ) from Trigger,

If I get the signal I will read from database and I will get the updates, If I get API I will parse API and get update

How can I do this?

Please help me.

Answer 1

Use PostgreSQL's NOTIFY statement in the trigger and have the interested session invoke the LISTEN statement. LISTEN doesn't block. It simply registers interest in a named condition. Messages will be delivered asynchronously to that session when the trigger calls NOTIFY with the same condition name.

In Perl, you probably won't use the LISTEN statement directly. DBD::Pg, for instance, provides a specific mechanism, pg_notifies, to achieve the same effect.