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

От:

raghupradeep <raghupradeep@gmail.com>

Дата:

12 апреля 2012 г. в 07:58:55

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?

Thanks & Regards
Raghupradeep

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Writing-data-to-a-text-file-based-on-a-trigger-event-tp5635290p5635290.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

От:

Vincent Veyron <vv.lists@wanadoo.fr>

Дата:

16 апреля 2012 г. в 08:02:53

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

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

От:

Tomas Vondra <tv@fuzzy.cz>

Дата:

15 апреля 2012 г. в 10:43:18

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

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

От:

François Beausoleil <francois@teksol.info>

Дата:

12 апреля 2012 г. в 09:29:44



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

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

От:

Jasen Betts <jasen@xnet.co.nz>

Дата:

14 апреля 2012 г. в 21:31:52

On 2012-04-12, 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?

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

-- 
⚂⚃ 100% natural