Re: Writing data to a text file based on a trigger event...

On 12.4.2012 12:58, raghupradeep wrote:
> Hi ,
>
> I am basically a MySQL DBA and have little idea on PostgreSQL. In our
> environment we have an application which is using PostgreSQL as its back
> end. The application logs the status of the jobs running in it to a table in
> this database i.e when a job starts it inserts a new row to this table and
> it keeps on updating the column `status` based on the status of the job
> running. So the requirement that we have is I need to capture certain status
> values and based on it need to through alert to our centralized monitoring
> system.
>
> What I need to know is it possible to write a trigger which will write the
> data of the row whose status column gets updated to a text file?

Hi,

as the others already suggested, it's much easier to do this inside a
database, i.e. storing the data inside the database itself and writing a
simple script to read them. Most monitoring systems I'm aware of (e.g.
nagios) support this out of the box.

But if you really need to write the data to a file, you may look at this
contrib module (called "extension" since 9.1)

http://www.postgresql.org/docs/9.1/interactive/adminpack.html

You may either use that directly or use that as an inspiration to write
your own C extension (it's quite simple).

Just be careful about granting the execution rights to regular users,
it's probably better to wrap the function in your own functions with
fixed (or properly checked) filenames.

Tomas