Como Concatenar Strings em uma Consulta Group By no PostgreSQL

Ao trabalhar com bancos de dados, particularmente com PostgreSQL, você pode se deparar com um cenário comum onde precisa concatenar strings dentro de um grupo de registros. Isso geralmente ocorre quando você possui um conjunto de dados que inclui várias entradas para uma única categoria (como funcionários na mesma empresa) e deseja coletar essas entradas em uma única string para apresentação ou análise.

Este post do blog explicará como alcançar a concatenação de strings com uma consulta GROUP BY no PostgreSQL. Vamos cobrir soluções modernas, bem como abordagens para versões mais antigas do PostgreSQL para um entendimento abrangente.

O Problema

Por exemplo, considere a seguinte tabela de funcionários:

Você deseja agrupar essas entradas por COMPANY_ID de modo que a saída apresente uma lista de funcionários associados a cada empresa. A saída desejada teria a seguinte aparência:

A Solução

PostgreSQL 9.0 ou Mais Recente

Se você estiver usando a versão 9.0 ou posterior do PostgreSQL, pode usar a poderosa função embutida string_agg() para concatenar strings dentro da sua cláusula GROUP BY de forma eficaz.

Aqui está como você pode escrever a consulta:

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

Com Ordenação

Além disso, como a versão 9.0 do PostgreSQL suporta ORDER BY dentro de funções agregadas, você pode especificar a ordem dos funcionários:

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

Isso garante que os nomes dos seus funcionários sejam concatenados em uma sequência específica.

PostgreSQL 8.4.x

Se você ainda estiver trabalhando com o PostgreSQL 8.4, que é uma versão que não recebe mais suporte, pode usar a função array_agg() em combinação com array_to_string() para alcançar resultados similares. Aqui está a consulta SQL que você usaria:

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

PostgreSQL 8.3.x e Versões Anteriores

Para usuários do PostgreSQL 8.3 e versões anteriores, não existe uma função embutida para realizar a concatenação de strings diretamente. A seguinte implementação personalizada pode contornar essa limitação:

1. Crie uma nova função de agregação usando a função textcat:

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

2. Para incluir separadores como , entre strings concatenadas, crie uma função personalizada:

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;

Isso irá concatenar as strings de forma apropriada.

Se você deseja remover vírgulas extras para valores NULL ou vazios, aqui está uma versão mais refinada:

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;

Conclusão

Concatenar strings no PostgreSQL, especialmente durante operações GROUP BY, é crucial para gerar relatórios e conjuntos de dados significativos. Com a introdução de funções como string_agg() em versões recentes, essa tarefa se tornou direta.

Se você está em uma versão mais antiga, usar os métodos de agregação personalizados detalhados aqui pode alcançar resultados similares. Sempre considere atualizar sua versão do PostgreSQL para aproveitar novos recursos e melhorias de segurança.

Boas consultas!