Comment concaténer des chaînes d’un champ de chaîne dans une requête ‘group by’ de PostgreSQL?

PostgreSQL 9.0 ou version ultérieure:

Les versions récentes de Postgres (depuis fin 2010) ont la fonction ssortingng_agg(expression, delimiter) qui fera exactement ce que la question demandait, vous permettant même de spécifier la chaîne de délimiteur:

 SELECT company_id, ssortingng_agg(employee, ', ') FROM mytable GROUP BY company_id; 

Postgres 9.0 a également ajouté la possibilité de spécifier une clause ORDER BY dans toute expression d’agrégat ; sinon, la commande n’est pas définie. Donc, vous pouvez maintenant écrire:

 SELECT company_id, ssortingng_agg(employee, ', ' ORDER BY employee) FROM mytable GROUP BY company_id; 

Ou bien:

 SELECT ssortingng_agg(actor_name, ', ' ORDER BY first_appearance) 

PostgreSQL 8.4 ou version ultérieure:

PostgreSQL 8.4 (en 2009) a introduit la fonction d’agrégat array_agg(expression) qui concatène les valeurs dans un tableau. Puis array_to_ssortingng() peut être utilisé pour donner le résultat souhaité:

 SELECT company_id, array_to_ssortingng(array_agg(employee), ', ') FROM mytable GROUP BY company_id; 

ssortingng_agg pour les versions antérieures à 9.0:

Dans le cas où quelqu’un est à la recherche d’une cale de compatibilité pour les bases de données antérieures à 9.0, il est possible d’implémenter tout dans ssortingng_agg à l’exception de la clause ORDER BY .

Donc, avec la définition ci-dessous, cela devrait fonctionner comme dans un DB Postgres 9.x:

 SELECT ssortingng_agg(name, '; ') AS semi_colon_separated_names FROM things; 

Mais ce sera une erreur de syntaxe:

 SELECT ssortingng_agg(name, '; ' ORDER BY name) AS semi_colon_separated_names FROM things; --> ERROR: syntax error at or near "ORDER" 

Testé sur PostgreSQL 8.3.

 CREATE FUNCTION ssortingng_agg_transfn(text, text, text) RETURNS text AS $$ BEGIN IF $1 IS NULL THEN RETURN $2; ELSE RETURN $1 || $3 || $2; END IF; END; $$ LANGUAGE plpgsql IMMUTABLE COST 1; CREATE AGGREGATE ssortingng_agg(text, text) ( SFUNC=ssortingng_agg_transfn, STYPE=text ); 

Variations personnalisées (toutes les versions de Postgres)

Avant 9.0, il n’y avait pas de fonction d’agrégation intégrée pour concaténer les chaînes. L’implémentation personnalisée la plus simple ( suggérée par Vajda Gabo dans cet article , parmi beaucoup d’autres) consiste à utiliser la fonction textcat intégrée (qui se trouve derrière l’opérateur || ):

 CREATE AGGREGATE textcat_all( basetype = text, sfunc = textcat, stype = text, initcond = '' ); 

Voici la documentation CREATE AGGREGATE .

Cela colle simplement toutes les chaînes sans séparateur. Pour obtenir un “,” inséré entre eux sans l’avoir à la fin, vous pouvez créer votre propre fonction de concaténation et la remplacer par le “textcat” ci-dessus. Voici celui que j’ai mis en place et testé le 8.3.12:

 CREATE FUNCTION commacat(acc text, instr text) RETURNS text AS $$ BEGIN IF acc IS NULL OR acc = '' THEN RETURN instr; ELSE RETURN acc || ', ' || instr; END IF; END; $$ LANGUAGE plpgsql; 

Cette version affichera une virgule même si la valeur de la ligne est nulle ou vide, vous obtenez donc une sortie comme ceci:

 a, b, c, , e, , g 

Si vous préférez supprimer des virgules supplémentaires pour générer ceci:

 a, b, c, e, g 

Ajoutez ensuite une vérification ELSIF à la fonction comme ceci:

 CREATE FUNCTION commacat_ignore_nulls(acc text, instr text) RETURNS text AS $$ BEGIN IF acc IS NULL OR acc = '' THEN RETURN instr; ELSIF instr IS NULL OR instr = '' THEN RETURN acc; ELSE RETURN acc || ', ' || instr; END IF; END; $$ LANGUAGE plpgsql; 

Que diriez-vous d’utiliser les fonctions de tableau intégrées Postgres? Au moins sur 8.4, cela fonctionne parfaitement:

 SELECT company_id, array_to_ssortingng(array_agg(employee), ',') FROM mytable GROUP BY company_id; 

A partir de PostgreSQL 9.0, vous pouvez utiliser la fonction d’agrégation appelée ssortingng_agg . Votre nouveau SQL devrait ressembler à ceci:

 SELECT company_id, ssortingng_agg(employee, ', ') FROM mytable GROUP BY company_id; 

Je ne réclame aucun crédit pour la réponse car je l’ai trouvée après quelques recherches:

Ce que je ne savais pas, c’est que PostgreSQL vous permet de définir vos propres fonctions d’agrégation avec CREATE AGGREGATE

Cet article sur la liste PostgreSQL montre à quel point il est sortingvial de créer une fonction pour faire ce qui est requirejs:

 CREATE AGGREGATE textcat_all( basetype = text, sfunc = textcat, stype = text, initcond = '' ); SELECT company_id, textcat_all(employee || ', ') FROM mytable GROUP BY company_id; 

Comme déjà mentionné, la création de votre propre fonction d’agrégation est la bonne chose à faire. Voici ma fonction d’agrégation de concaténation (vous pouvez trouver des détails en français ):

 CREATE OR REPLACE FUNCTION concat2(text, text) RETURNS text AS ' SELECT CASE WHEN $1 IS NULL OR $1 = \'\' THEN $2 WHEN $2 IS NULL OR $2 = \'\' THEN $1 ELSE $1 || \' / \' || $2 END; ' LANGUAGE SQL; CREATE AGGREGATE concatenate ( sfunc = concat2, basetype = text, stype = text, initcond = '' 

);

Et puis l’utiliser comme:

 SELECT company_id, concatenate(employee) AS employees FROM ... 

Ce dernier extrait de la liste des annonces pourrait vous intéresser si vous passiez à la version 8.4:

Jusqu’à ce que 8.4 apparaisse avec une version native très efficace, vous pouvez append la fonction array_accum () dans la documentation PostgreSQL pour transférer n’importe quelle colonne dans un tableau, qui peut ensuite être utilisé par le code de l’application ou associé à array_to_ssortingng () comme une liste:

http://www.postgresql.org/docs/current/static/xaggr.html

Je vais créer un lien vers les documents de développement 8.4, mais ils ne semblent pas encore lister cette fonctionnalité.

Suite à la réponse de Kev, en utilisant les documents de Postgres:

Tout d’abord, créez un tableau des éléments, puis utilisez la fonction array_to_ssortingng intégrée.

 CREATE AGGREGATE array_accum (anyelement) ( sfunc = array_append, stype = anyarray, initcond = '{}' ); select array_to_ssortingng(array_accum(name),'|') from table group by id; 

Suite à l’utilisation d’une fonction d’agrégation personnalisée de la concaténation de chaînes: vous devez vous rappeler que l’instruction select placera les lignes dans n’importe quel ordre, vous devrez donc faire une sous- sélection dans l’instruction from avec une clause order by et puis une sélection externe avec une clause group by pour agréger les chaînes, donc:

 SELECT custom_aggregate(MY.special_ssortingngs) FROM (SELECT special_ssortingngs, grouping_column FROM a_table ORDER BY ordering_column) MY GROUP BY MY.grouping_column 

J’ai trouvé cette documentation PostgreSQL utile: http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html .

Dans mon cas, j’ai cherché du code SQL simple pour concaténer un champ entre parenthèses, si le champ n’est pas vide.

 select itemid, CASE itemdescription WHEN '' THEN itemname ELSE itemname || ' (' || itemdescription || ')' END from items; 

Selon la version PostgreSQL 9.0 et les versions ultérieures, vous pouvez utiliser la fonction d’agrégation appelée ssortingng_agg. Votre nouveau SQL devrait ressembler à ceci:

 SELECT company_id, ssortingng_agg(employee, ', ') FROM mytable GROUP BY company_id; 

Vous pouvez également utiliser la fonction de formatage. Qui peut aussi implicitement prendre en charge la conversion de type de texte, int, etc. par lui-même.

 create or replace function concat_return_row_count(tbl_name text, column_name text, value int) returns integer as $row_count$ declare total integer; begin EXECUTE format('select count(*) from %s WHERE %s = %s', tbl_name, column_name, value) INTO total; return total; end; $row_count$ language plpgsql; postgres=# select concat_return_row_count('tbl_name','column_name',2); --2 is the value