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

Le jeudi 12 avril 2012 à 06:58, raghupradeep a écrit :

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?

I would advise simply INSERTing the old values into a new table. This would be more flexible in the end, as you could export to a file, or run queries, or whatever.

The trigger would be an ON UPDATE, and very similar to the auditing trigger at http://www.postgresql.org/docs/9.1/static/plpgsql-trigger.html#PLPGSQL-TRIGGER-AUDIT-EXAMPLE

Welcome to PostgreSQL! Hope that helps!
François

On 2012-04-12, raghupradeep <raghupradeep@gmail.com> 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?

you mean like "RAISE LOG" does? or do you have specific requirements?

--
⚂⚃ 100% natural

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

Le dimanche 15 avril 2012 ￜ 15:43 +0200, Tomas Vondra a ￜcrit :

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).

I use plperlu in the function below (update_coll_list) to rewrite a
series of files; it is used by a trigger on the table (tblcollectivite).
The function only rewrites the files if one particular field (libelle)
was modified.

Documentation is here :
http://www.postgresql.org/docs/9.1/interactive/plperl.html

CREATE TRIGGER "tblcollectivite_after_update" AFTER UPDATE OR DELETE OR
INSERT ON tblcollectivite FOR EACH ROW EXECUTE PROCEDURE
update_coll_list();

CREATE OR REPLACE FUNCTION update_coll_list() RETURNS TRIGGER AS $$
#fonction de re-crï¿œation des listes alphabï¿œtiques des collectivitï¿œs

#inutile de tout rￜￜcrire si le libelle n'a pas changￜ
return if ( ( $_TD->{event} eq 'UPDATE' ) and
( $_TD->{new}{libelle} eq $_TD->{old}{libelle}) );

my $id_client = ( $_TD->{event} eq 'DELETE' ) ?
$_TD->{old}{id_client} : $_TD->{new}{id_client};

#rï¿œpertoire de stockage des fichiers ï¿œcrits par la procï¿œdure
my $storage_dir =
"/home/www_aspro/base/liste_collectivites/$id_client";

#la requï¿œte qui ramï¿œne les donnï¿œes
my $rv = spi_exec_query("SELECT id_collectivite, libelle FROM
tblcollectivite WHERE id_client=$id_client ORDER BY 2");

#le fichier 'all' qui liste toutes les collectivitï¿œs
open my $fh, ">$storage_dir/all" or elog(ERROR, qq{could not open
file $storage_dir/all : $?});

my %list;

#exï¿œcuter la requï¿œte, compter les lignes
my $nrows = $rv->{processed};

#pour chaque ligne, imprimer le nom
foreach my $rn (0 .. $nrows - 1) {

my $row = $rv->{rows}[$rn];

my $libelle = $row->{id_collectivite} . ';' . $row->{libelle} . "\n";

print $fh $libelle;

my $initial = lc(substr($row->{libelle},0,1));

die "non alphabï¿œtique : $libelle" if $initial !~/[a-z]/i;

$list{$initial} .= $libelle;

#elog(INFO, qq {$list{$initial} });

}

close $fh;

for ('a'..'z') {

my $initial_file = $storage_dir . '/' . $_;

open my $new_fh, ">$initial_file" or elog(ERROR, qq{could not open
file $initial_file : $!});

print $new_fh $list{$_};

#elog(INFO, qq {file: $initial_file / $list{$_} });

close $new_fh;

}

return;

$$ LANGUAGE plperlu;

--
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres assurances et des dossiers contentieux pour le service juridique