Re: Duplication des données insérées dans un fichier

Pour s'en sortir avec le copy, il faut le mettre dans un execute (testé à
l'instant sur une 8.3) ...

CREATE OR REPLACE FUNCTION copy_on_fly () RETURNS trigger AS $copy_on_fly$
DECLARE
csv_file VARCHAR(100);
nb INT;
BEGIN
csv_file := '/tmp/data_' || NEW.id || '.csv';
-- Trace pour demo
RAISE NOTICE 'csv_file = %', csv_file;
-- Code ne servant qu'a verifier l'interpretation correcte de NEW.id
nb = ( SELECT COUNT( p.* ) FROM person AS p WHERE p.id <> NEW.id );
-- Trace pour demo
RAISE NOTICE 'nb = %', nb;
EXECUTE 'COPY ( SELECT p.* FROM person AS p WHERE p.id = ' ||
NEW.id || ' )
TO ''' || csv_file || '''
WITH DELIMITER AS ''|''' ;
RETURN NEW;
END;
$copy_on_fly$ LANGUAGE 'plpgsql';

Evidemment, avec les escapes des simple quotes, ca manque d'élégance, mais ca
marche ...

Bonne journée

Le Tuesday 11 November 2008 00:05:30 Sébastien Dinot, vous avez écrit :
> Bonjour à tous,
>
> Un collègue m'a posé aujourd'hui une colle inhabituelle sur PostgreSQL
> et je ne suis pas certain de savoir y répondre, ni même que PostgreSQL
> sache y répondre. Je relaie donc ici sa demande pour savoir ce que vous
> en pensez.
>
> Avant que vous n'hurliez au scandale et à l'ineptie, je précise tout de
> suite que mon collègue doit introduire une base de données dans une
> architecture applicative complexe, faisant intervenir des dizaines de
> composants et qu'il n'est évidemment pas question de tout réécrire pour
> l'occasion. Il doit donc faire le plus possible avec l'existant,
> situation inconfortable que vous devez tous fort bien connaître. )c:
>
> Tout commence de manière très banale : des applications produisent des
> données qu'elles injectent en base. Mais les choses se compliquent
> rapidement : parmi les applications susceptibles de consommer ces
> données, s'en trouvent quelques unes incapables de se connecter à la
> base. Ces applications attendent les données sous la forme de fichiers
> CSV déposés dans un répertoire qu'elles scrutent périodiquement au
> travers d'une connexion FTP.
>
> Mon collègue m'a donc demandé comment mettre le plus rapidement possible
> les données insérées en base à disposition de ces applications.
>
> Faute de mieux, je lui ai dit que la solution passait peut-être par un
> trigger sur insertion invoquant la commande « COPY TO », un truc du
> genre :
>
> ------------------------------------------------------------------------
> CREATE SEQUENCE seq_person_id;
>
> CREATE TABLE person
> (
> id INT DEFAULT nextval('seq_person_id') NOT NULL UNIQUE,
> firstname VARCHAR(30) NOT NULL,
> lastname VARCHAR(50) NOT NULL
> );
>
>
> CREATE FUNCTION copy_on_fly () RETURNS trigger AS $copy_on_fly$
>
> DECLARE
> csv_file VARCHAR(100);
> nb INT;
>
> BEGIN
> csv_file := '/tmp/data_' || NEW.id || '.csv';
> -- Trace pour demo
> RAISE NOTICE 'csv_file = %', csv_file;
>
> -- Code ne servant qu'a verifier l'interpretation correcte de NEW.id
> nb = ( SELECT COUNT( p.* ) FROM person AS p WHERE p.id <> NEW.id );
> -- Trace pour demo
> RAISE NOTICE 'nb = %', nb;
>
> COPY ( SELECT p.* FROM person AS p WHERE p.id = NEW.id )
> TO csv_file
> WITH DELIMITER AS '|' ;
>
> RETURN NEW;
> END;
>
> $copy_on_fly$ LANGUAGE 'plpgsql';
>
>
> CREATE TRIGGER trigger_copy_on_fly
> AFTER INSERT ON person
> FOR EACH ROW EXECUTE PROCEDURE copy_on_fly();
>
>
> INSERT INTO person ( firstname, lastname ) VALUES ( 'Albert', 'ALPHA' );
> INSERT INTO person ( firstname, lastname ) VALUES ( 'Bernard', 'BRAVO' );
> INSERT INTO person ( firstname, lastname ) VALUES ( 'Celine', 'CHARLIE' );
> ------------------------------------------------------------------------
>
> Mais le code prédédent ne fonctionne pas.
>
> 1. La variable csv_file n'est pas reconnue dans :
>
> COPY ... TO csv_file
>
> Le message d'erreur est :
>
> =====================================================================
> psql:data.sql:31: ERREUR: erreur de syntaxe sur ou près de « $2 »
> LINE 1: ...LECT p.* FROM person AS p WHERE p.id = $1 ) TO $2 WITH
> D... ^
> QUERY: COPY ( SELECT p.* FROM person AS p WHERE p.id = $1 ) TO $2
> WITH DELIMITER AS '|' CONTEXT: SQL statement in PL/PgSQL function
> "copy_on_fly" near line 15
> =====================================================================
>
>
> 2. Lorsqu'on remplace la variable csv_file par une chaîne en dur, un
> autre problème survient. Alors que NEW.id est reconnu dans :
>
> nb = ( SELECT COUNT( p.* ) FROM person AS p WHERE p.id = NEW.id );
>
> Il ne n'est pas dans :
>
> COPY ( SELECT p.* FROM person AS p WHERE p.id = NEW.id ) ...
>
> Le message d'erreur est :
>
> =====================================================================
> psql:data.sql:39: NOTICE: csv_file = /tmp/data_1.csv
> psql:data.sql:39: NOTICE: nb = 1
> psql:data.sql:39: ERREUR: Il n'existe pas de paramètres $1
> CONTEXT: instruction SQL « COPY ( SELECT p.* FROM person AS p WHERE p.id =
> $1 ) TO '/tmp/data.csv' WITH DELIMITER AS '|' » PL/pgSQL function
> "copy_on_fly" line 8 at SQL statement
> =====================================================================
>
> 3. Si l'on commente la commande COPY ... TO, l'insertion fonctionne et
> produit l'affichage :
>
> =====================================================================
> psql:data.sql:39: NOTICE: csv_file = /tmp/data_1.csv
> psql:data.sql:39: NOTICE: nb = 0
> INSERT 0 1
> psql:data.sql:40: NOTICE: csv_file = /tmp/data_2.csv
> psql:data.sql:40: NOTICE: nb = 1
> INSERT 0 1
> psql:data.sql:41: NOTICE: csv_file = /tmp/data_3.csv
> psql:data.sql:41: NOTICE: nb = 2
> INSERT 0 1
> =====================================================================
>
>
> Mes questions sont donc multiples :
>
> - Qu'est-ce qui ne va pas dans mon code ? J'ai cherché mais je ne trouve
> pas. )c:
>
> - Pensez-vous que cette solution soit implantable et viable ?
>
> - Sinon, que feriez-vous ?
>
> Je vous remercie par avance de vos lumières,
>
> Sébastien
>
>
> --
> Sébastien Dinot, sebastien(dot)dinot(at)free(dot)fr
> http://sebastien.dinot.free.fr/
> Ne goûtez pas au logiciel libre, vous ne pourriez plus vous en passer !