Salut !
 
j'ai eu quelques problèmes à résoudre le problème suivant, et j'aurais voulu savoir si vous aviez de meilleurs idées ...
 
 
Voilà le problème :

• j'ai une table 'villes', avec dedans, un colonne 'nom' de type text, et une colonne 'code_postal', de type text aussi. La table contient donc une liste de ville et le code postal associée à cette ville
• j'aurais voulu récupérer pour chaque département, les X (premières) villes (disons, par ordre de code postal, mais c'est pas vraiment important)

Donc imaginons que j'ai l'équivalent de 90 départements dans ma base, si je veux récupérer les 10 premières villes de chaque département, je dois récupérer 900 couples ville/code_postal.
 
 
Question : comment je fais ?
 
 
J'ai essayé plusieurs trucs :

• récupérer le code postal, facile. Mais j'arrive pas à en faire grand chose
• faire des tests en prenant que deux départements. Ça donnait quelque chose dans ce gout là (attention les yeux) :

SELECT commune, code_postal
FROM (
    SELECT nom, code_postal
    FROM villes
    WHERE code_postal LIKE '01%'
    LIMIT 5
) AS t
 
UNION
 
SELECT commune, code_postal 
FROM
(
    SELECT nom, code_postal
    FROM villes
    WHERE code_postal LIKE '02%'
    LIMIT 5
) AS t
 
ORDER BY code_postal

... mais bon, à part coller 90 fois ce code, pas terrible non plus.
 
Là, j'ai réussi à me démerder avec une procédure stockée, qui fait ce copier/coller (en fait). Ça génère une grosse requête, que j'exécute dans un curseur, puis je retourne le curseur et je fais un FETCH ALL dessus .
Ça me semble un peu gorret
 
 
Donc voilà, est-ce que vous auriez une meilleure solution ?

Ya pas de roxorz du SQL par là ?

HFR c'est plus ce que c'était

grmpf.
 
je pars fumer une clope, si je me souvient de ton problème à mon retour, je regarde

Voilà, inspire toi de ce petit test que je viens de faire :
 

SELECT *
FROM
(
SELECT tie.typtie, tie.sigtie, 
sum(1) over (partition BY tie.typtie ORDER BY tie.typtie, tie.sigtie rows unbounded preceding) AS incr 
FROM tie
WHERE tie.codsoc = 2 -- c'est juste un filtre pour faire joli
ORDER BY tie.typtie, tie.sigtie
) tmp
WHERE tmp.incr <= 5 -- je veux les 5 premiers par groups de tie.typtie
ORDER BY typtie, sigtie

 
Ici, c'est sous Oracle 10gR2.
Je ne garanti pas que ce soit transposable 100% de façon identique sous PostGreSQL.
 
Ceci dit, il s'agit uniquement de mots clés SQL-92, donc PostGreSQL respectant très bien cette norme, tu peux avoir bon espoir.

tu peux aussi essayer de te démerder avec la doc de T-SQL, puisque la syntaxe est différente (et là j'ai pas le temps de regarder comment ça marche avec sql server)
http://msdn2.microsoft.com/fr-fr/library/ms189461.aspx

Salut !
 
Merci d'avoir répondu
 
Bon, par contre, ça m'aide pas trop, puisque Postgres ne support pas les fonctions sur des fenêtres (le sum(1) OVER (...) )
 
Ceci dit, je vois un peu comment tu faisais ça, je vais voir si je peux trouver un moyen de faire la même chose sans passer par ces fonctionnalités ...

Ben sinon, tu fais une PS qui te retourne sous forme de table les 10 première villes d'un département. En faisant un inner join dessus à partir des départements, c'est gagné.

Pas bête, c'était presque bon

CREATE OR REPLACE FUNCTION city_by_dep(dep integer) RETURNS SETOF villes AS $$
DECLARE
    line RECORD;
BEGIN
    FOR line IN SELECT * FROM villes WHERE code_postal LIKE dep || '%' ORDER BY code_postal LIMIT 10 LOOP
        RETURN next line;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

et :

SELECT *
FROM (
    SELECT substring(code_postal FOR 2) AS dep FROM villes GROUP BY dep ORDER BY dep
) AS t
JOIN city_by_dep(t.dep) ON true

mais bon, apparement, j'ai pas le droit d'utiliser "t.dep" dans l'appel à la procédure

Bon, ceci dit, ça m'a permis du coup de comprendre comment on pouvait récupérer plusieurs lignes à partir d'un appel à une procédure

c'est exprès la faute de frappe dans la ligne 5 de ta fonction ?
 
parceque sinon, mise à part limitation praticulière de PG, et syntaxe mise à part (je maîtrise plutôt le T-SQL qui diffère énormément), le principe me semble tout à fait bon.
 
je vérifierai demain, mais il me semble que j'utilise justement ce système au boulot (pour une toute autre raison) et ça passe sans problème sous sql server. ce serait bien con que ça ne passe pas sous pg

oups, en passant les mots clés en majuscule, j'ai oublié un E
 

Bon, après, comme j'ai dis, j'ai découvert tout à l'heure comment utiliser une fonction pour récupérer plusieurs résultats, donc chuis ptêtre pas forcément super au point non plus.
 
Je poserais la question sur la ML postgresql-user pour voir si quelqu'un connaissant bien Postgres a une meilleure solution.
 
 
Merci

Bon, j'ai fait mumuse tout à l'heure, et je me suis rendu compte que... sous SQL Server non plus on peut pas faire de jointure sur une fonction de ce genre... du moins les paramètres attendus ne peuvent pas provenir des autres tables.
 
Bref.
 
Voici un script complet qui marche chez moi :
 

DROP FUNCTION Get10ComPerDep;
DROP TABLE com;
DROP TABLE dep;
 
CREATE TABLE dep
(
  nomdep char(23) NOT NULL PRIMARY KEY,
  numdep char(2) NOT NULL
);
CREATE TABLE com
(
  insee char(5) NOT NULL PRIMARY KEY,
  codpos char(5) NOT NULL,
  nomcom char(26) NOT NULL,
  nomdep char(23) NOT NULL REFERENCES dep(nomdep)
);
 
CREATE INDEX ix_nomcom ON com (nomcom);
CREATE INDEX ix_codpos ON com (codpos);
go
 
CREATE FUNCTION Get10ComPerDep()
returns @ret TABLE
(
  nomdep char(23),
  codpos char(5),
  nomcom char(26),
  insee char(5) PRIMARY KEY
)
AS
begin
  declare @nomdep char(23);
 
  declare curdep cursor FOR
  SELECT nomdep FROM dep;
 
  open curdep;
 
  fetch next FROM curdep INTO @nomdep;
 
  while @@fetch_status = 0
  begin
    INSERT INTO @ret (nomdep, codpos, nomcom, insee)
    SELECT top 10 @nomdep, codpos, nomcom, insee FROM com WHERE nomdep = @nomdep ORDER BY codpos;
 
    fetch next FROM curdep INTO @nomdep;
  end;
 
  close curdep;
  deallocate curdep;
 
  RETURN;
end;
go
 
INSERT INTO dep (nomdep, numdep)
SELECT DISTINCT departement, LEFT(codepos, 2)
FROM insee
ORDER BY 1;
 
INSERT INTO com (insee, codpos, nomcom, nomdep)
SELECT insee, codepos, commune, departement
FROM insee
ORDER BY 1;
 
SELECT *
FROM Get10ComPerDep()
ORDER BY nomdep, nomcom;

 
(bon, au début je me prends la tête pour recopier "proprement" une table "INSEE" récupérée sur le net dans deux tables "com" et "dep", n'y prête pas attention )

Ouais, j'avais pensé à faire un truc comme ça au début en fait ...
... sauf que je savais pas retourner plusieurs lignes d'un coup
 
D'où ma première "solution"  
 
Donc là, ouais, en faisant une boucle sur les départements, puis une requête pour récupérer 10 villes et RETURN NEXT sur le résultat, c'est cool