Можем ли мы определить функцию GROUP_CONCAT в PostgreSQL?

У меня есть несколько строк кода SQL для моей старой базы данных с операторами GROUP_CONCAT, например:

SELECT SUM(age), GROUP_CONCAT(sal) FROM Users;

В PostgreSQL я могу сделать то же самое:

SELECT SUM(age), string_agg(sal, ', ') FROM Users;

Я хотел бы как можно больше повторно использовать старый SQL. Поэтому мне нужно определить функцию GROUP_CONCAT, которая внутренне вызывает string_agg. Возможно ли это?

EDIT: связанный вопрос не имеет отношения!

В моем вопросе спрашивается: "Как определить функцию, называемую group_concat?". Связанный вопрос говорит: "Как мы можем сделать что-то эквивалентное группе concat (без определения новой функции)?". Ни один из ответов не соответствует тому, что я хочу.

Выбранный ответ на месте! К счастью, ответ был получен до закрытия.

Ответы

Ответ 1

Существует встроенная функция string_agg(), которая делает то, что вы хотите, но вы специально требуете, чтобы ее называли group_concat для совместимости с MySQL. К сожалению, string_agg() использует внутренний тип данных для накопления (предположительно, чтобы избежать копирования всего буфера на каждом добавлении, я не смотрел на источник, хотя), и я не нашел способ объявить SQL-aggrerate, идентичный string_agg ().

Определение функции group_concat() также не будет работать, так как pg должно быть осведомлено о том, что это агрегат, а не функция с совокупностью, скрытой внутри, которая не работает. Такая функция будет работать по одной строке за раз: любая совокупность внутри будет просто агрегировать одну строку и вернуть ее без изменений...

Таким образом, этот код будет накапливать элементы в массиве, а затем добавлять разделители "," с помощью array_to_string. Я буду использовать объявление array_agg() (прежде чем оно станет встроенным) в качестве модели и просто добавит функцию finalizer, которая преобразует агрегированный массив в текст.

CREATE OR REPLACE FUNCTION _group_concat_finalize(anyarray)
RETURNS text AS $$
    SELECT array_to_string($1,',')
$$ IMMUTABLE LANGUAGE SQL;

CREATE AGGREGATE group_concat(anyelement) (
   SFUNC=array_append,
   STYPE=anyarray,
   FFUNC=_group_concat_finalize,
   INITCOND='{}'
);

SELECT group_concat(x) FROM foo;

Приятно, что он должен работать нормально для любого типа, без хлопот, благодаря родовым типам "anyarray" и "anyelement".

Я бы предположил, что это будет медленнее, чем string_agg(), если string_agg действительно не позволяет скопировать весь массив агрегации на каждом добавлении. Это должно иметь значение только в том случае, если количество строк, которые будут сгруппированы в каждый набор, велико. В этом случае вы, вероятно, можете потратить минуту на редактирование SQL-запроса;)

http://sqlfiddle.com/#!17/c452d/1

Ответ 2

Да, это возможно. Посмотрите https://github.com/2ndQuadrant/mysqlcompat/blob/master/sql_bits/aggregate.sql, где он уже был выполнен:

-- GROUP_CONCAT()
-- Note: only supports the comma separator
-- Note: For DISTINCT and ORDER BY a subquery is required
CREATE OR REPLACE FUNCTION _group_concat(text, text)
RETURNS text AS $$
  SELECT CASE
    WHEN $2 IS NULL THEN $1
    WHEN $1 IS NULL THEN $2
    ELSE $1 operator(pg_catalog.||) ',' operator(pg_catalog.||) $2
  END
$$ IMMUTABLE LANGUAGE SQL;

CREATE AGGREGATE group_concat (
    BASETYPE = text,
    SFUNC = _group_concat,
    STYPE = text
);