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

From: Sébastien Dinot <sebastien(dot)dinot(at)free(dot)fr>
To: pgsql-fr-generale(at)postgresql(dot)org
Subject: Duplication des données insérées dans un fichier
Date: 2008-11-10 23:05:30
Message-ID: 20081110230530.GA7125@dinot.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-fr-generale

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 !

Responses

Browse pgsql-fr-generale by date

  From Date Subject
Next Message Dimitri Fontaine 2008-11-11 10:34:31 Re: Duplication des données insérées dans un fichier
Previous Message Marc Cousin 2008-11-10 15:27:25 Re: travailler sur D sans sollicité C