Forums PostgreSQL.fr

Maps

Membre

Export multiple en CSV par individu-année

Bonjour,

J'utilise PostGIS depuis déjà quelques mois (un vrai bonheur !). Pour autant, si je suis capable d'écrire des requêtes spatiales parfois complexes, les requêtes non spatiales les plus simples (purement PostgreSQL) me sont parfois inaccessibles. Et je crois que c'est le cas ici.

Je vais essayer de simplifier le problème : j'ai une table (appelons-la 'locs') avec notamment les champs 'id', 'year', 'x', 'y'. Je souhaiterais exporter cette table mais en créant un fichier CSV par id-year. Je suis capable de le faire manuellement, par exemple :

COPY (SELECT id, year, x, y FROM locs
WHERE year = '2010' AND id = 'CA12')
TO '/home/postgres/test.csv' WITH CSV HEADER DELIMITER ',';

Mais je n'arrive pas à boucler tout ça pour le faire en une seul requête pour tous les id-year (j'ai pas mal d'individus-années, et je dois ensuite le répliquer avec quelques variantes). J'ai regardé du côté des boucles sous PostgreSQL (notamment FOR), mais sans succès. Il me semble que les boucles sont prévues pour évaluer une fonction ligne à ligne...

Je suis capable de récupérer les 'id' (ou les années) uniques avec

SELECT DISTINCT id FROM locs;

mais je ne sais pas du tout quoi en faire ensuite... En bref, je n'ai aucune idée d'où regarder en ce moment (je n'ai quasi aucun automatisme pour PostgreSQL et j'ai l'impression de chercher au hasard, avec des mots-clés qui ne sont pas pertinents).

Je suis donc preneur de toute information, piste ou conseil ! Merci d'avance !
Maps

gleu

Administrateur

Re : Export multiple en CSV par individu-année

Si vous avez une version 9.0 ou ultérieure, vous pouvez faire ceci :

do language plpgsql $do$
declare
a integer;
req text;
begin
for a in select distinct year from locs order by year
loop
  raise log '%', a;
  execute 'COPY (SELECT id, year, x, y FROM locs '
    || 'WHERE year = ' || a || ' AND id = ''CA12'') '
    || 'TO ''/tmp/test' || a || '.csv'' '
    || 'WITH CSV HEADER DELIMITER '','' ';
end loop;
end
$do$;

Sur les versions antérieures, je ne vois pas de solution en dehors de la création d'une procédure stockée (qui reprend en gros le code ci-dessus).

---

Guillaume.

Maps

Membre

Re : Export multiple en CSV par individu-année

Ouh... J'étais donc bien sur la bonne piste avec le FOR, mais j'étais encore bien loin du compte. C'est sensiblement plus complexe que ce que j'imaginais ! Pour autant, la syntaxe reste assez simple et intuitive. Malheureusement, le serveur tourne avec PostgreSQL 8.4.9... Est-ce grave, docteur ?

Je ne suis pas trop sûr, mais avec une procédure embarquée, on procéderait peut-être comme ceci (j'ai essayé d'adapter un code trouvé sur le net) :

CREATE OR REPLACE FUNCTION testexport() AS
$BODY$
declare
a integer;
req text;
begin
for a in select distinct year from locs order by year
loop
  raise log '%', a;
  execute 'COPY (SELECT id, year, x, y FROM locs '
    || 'WHERE year = ' || a || ' AND id = ''CA12'') '
    || 'TO ''/tmp/test' || a || '.csv'' '
    || 'WITH CSV HEADER DELIMITER '','' ';
end loop;
end
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Dans cet exemple, est-ce que 'a' est forcément un entier ? Est-ce qu'on pourrait faire la même chose avec

FOR ind IN SELECT DISTINCT id FROM locs ORDER BY id

? Et ensuite imbriquer les 2 ?

Un gros merci pour cette réponse très rapide ! (ça paraît simple une fois expliqué !)
Maps

gleu

Administrateur

Re : Export multiple en CSV par individu-année

a n'est pas forcément un entier, ça peut être un texte ou tout autre type de données. Et, oui, il est possible d'imbriquer deux boucles FOR.

---

Guillaume.

Maps

Membre

Re : Export multiple en CSV par individu-année

a n'est pas forcément un entier, ça peut être un texte ou tout autre type de données. Et, oui, il est possible d'imbriquer deux boucles FOR.

Superbe ! Je vais tester ça dès que j'ai un peu de temps (dans le courant du week-end j'espère), et je reviendrais poster mes résultats !

Encore merci pour ces précisions rapides !
Maps.

Maps

Membre

Re : Export multiple en CSV par individu-année

Ça y est ! Je viens d'écrire ma première fonction PL/pgSQL, avec une émotion non feinte !

Voilà donc ce que ça donne :

CREATE OR REPLACE FUNCTION exportIdYear(tablocs varchar) RETURNS VOID AS
$BODY$
DECLARE
  id text;
  year text;
  req text;
BEGIN
  FOR id IN EXECUTE 'SELECT DISTINCT id FROM ' || quote_ident(tablocs) 
    || ' ORDER BY id'
  LOOP
    RAISE LOG '%',  id;
    FOR year IN EXECUTE 'SELECT DISTINCT to_char(date, ''YYYY'') AS year FROM ' 
      || quote_ident(tablocs) || ' WHERE id = ' || quote_literal(id) 
      || ' ORDER BY year'
    LOOP
      RAISE LOG '%',  year;
      EXECUTE 'COPY (SELECT id,date, x,y, FROM '
      || quote_ident(tablocs)
      || ' WHERE id = ' || quote_literal(id) 
      || ' AND to_char(date, ''YYYY'') = ' || quote_literal(year) || ') '
      || 'TO ''/home/postgres/' || id || '-' || year || '.csv'' '
      || 'WITH CSV HEADER DELIMITER '','' ';
    END LOOP;
  END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Que j'appelle sur une table 'bla' avec un simple :

SELECT exportIdYear('bla');

Un grand merci en tout cas pour vos réponses qui m'ont mis sur la bonne voie !
Maps

Dernière modification par Maps (12/12/2011 01:36:52)