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

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