Re: Writing data to a text file based on a trigger event... - Mailing list pgsql-general

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