我们可以在PostgreSQL中定义GROUP_CONCAT函数吗？ [英] Can we define a GROUP_CONCAT function in PostgreSQL?

问题描述

对于带有 GROUP_CONCAT 语句的旧数据库，我有几行SQL代码，例如：

 从用户中选择SUM（age），GROUP_CONCAT（sal）； 
  

在PostgreSQL中，我可以这样做：

 从用户处选择SUM（age），string_agg（sal，'，'）; 
  

我想尽可能地重用旧的SQL。因此，我需要定义一个 GROUP_CONCAT 函数，该函数在内部调用 string_agg 。这可能吗？

编辑：链接的问题无关！

我的问题问如何定义一个函数称为group_concat？。链接的问题说：我们如何做等同于concat的事情（不定义新功能）？。没有答案，也符合我的要求。

选定的答案就在眼前！值得庆幸的是，它在关闭之前得到了答复。

解决方案

有一个内置的string_agg（）可以满足您的要求，但是您特别要求将其命名为MySQL的group_concat兼容性。不幸的是，string_agg（）使用内部数据类型进行累积（大概是为了避免在每个追加上复制整个缓冲区，尽管我没有查看源代码），但我没有找到一种方法来声明与string_agg（ ）。

定义group_concat（）函数也不起作用，因为必须让pg知道它是一个集合，而不是隐藏在里面的集合函数，将无法正常工作。这样的函数一次只能在一行上运行：内部的任何聚合都只会聚合一行并返回不变...

因此，此代码将累加元素放入数组中，然后使用array_to_string添加，定界符。我将使用array_agg（）声明（在成为内置声明之前）作为模型，并简单地添加一个终结器函数，该函数将聚合数组转换为文本。

 创建或替换功能_group_concat_finalize（anyarray）
返回文本AS $$ 
选择array_to_string（$ 1，'，'）
 $ IMMUTABLE LANGUAGE SQL; 
 
创建集合group_concat（anyelement）（
 SFUNC = array_append，
 STYPE = anyarray，
 FFUNC = _group_concat_finalize，
 INITCOND ='{}'
）; 
 
从foo中选择group_concat（x）; 
  

好的事情是，由于泛型类型，它对于任何类型都可以正常工作，而没有麻烦我猜想如果string_agg确实避免在每个附加项上复制整个聚合数组，那么这会比string_agg（）慢。但是，仅当要分组到每个集合中的行数很大时，这才有意义。在这种情况下，您可能需要花一点时间来编辑SQL查询;）

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

I have several lines of SQL code for my legacy database with GROUP_CONCAT statements, as in:

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

In PostgreSQL, I can do the same with:

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

I would like to reuse the old SQL as much as possible. So I need to define a GROUP_CONCAT function that internally calls string_agg. Is this possible?

EDIT: The linked question is unrelated!

My question asks "How to define a function called group_concat?". The linked question says "How can we do something equivalent to group concat (without defining a new function)?". None of the answers, also match what I want.

The selected answer is spot on! Thankfully it was answered before getting closed.

解决方案

There is a string_agg() builtin which does what you want, but you specifically ask for it to be named group_concat for MySQL compatibility. Unfortunately, string_agg() uses an internal data type for accumulation (presumably to avoid copying the whole buffer on each append, I have not looked at the source though) and I didn't find a way to declare a SQL aggrerate identical to string_agg().

Defining group_concat() function would not work either, as pg has to be made aware that it is an aggregate, not a function with an aggregate hidden inside, which would not work. Such a function would operate on one row at a time: any aggregate inside would just aggregate a single row and return it unchanged...

Thus, this code will accumulate the elements into an array, then add the "," delimiters with array_to_string. I will use the array_agg() declaration (before it became a built-in) as a model, and simply add a finalizer function which will convert the aggregated array into text.

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;

The nice thing is that it should work fine for any type, without hassle, thanks to the generic types "anyarray" and "anyelement".

I would presume this would be slower than string_agg() if string_agg does indeed avoid to copy the whole aggregation array on each append. This should matter only if the number of rows to be grouped into each set is large, though. In this case, you probably can spend a minute editing the SQL query ;)

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

这篇关于我们可以在PostgreSQL中定义GROUP_CONCAT函数吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！