add sql `STRING_AGG` function

[universalmind303]

Is your feature request related to a problem or challenge?

I'd like to be able to use the equivalent of the postgres function STRING_AGG.

Describe the solution you'd like

select string_agg(first_name, ',') as names from tbl group by last_name

Describe alternatives you've considered

No response

Additional context

a few references on string_agg
https://www.postgresqltutorial.com/postgresql-aggregate-functions/postgresql-string_agg-function/
https://www.postgresql.org/docs/9.0/functions-aggregate.html

[alamb]

FWIW string_agg I think is a table function, which DataFusion doesn't really have great support for yet.

What would you think about implementing something like User Defined Table Functions as a way to implement this? #2177

[universalmind303]

FWIW string_agg I think is a table function, which DataFusion doesn't really have great support for yet.

isn't string_agg is an aggregate function similar to ARRAY_AGG which is already a supported function?

string_agg should be functionally equivalent to the combination of already supported functions
concat_ws(<delimiter>, array_agg(<expr>)).

Maybe there's a different string_agg function in other dialects I'm not aware of?

[alamb]

isn't string_agg is an aggregate function similar to ARRAY_AGG which is already a supported function?

You are right, that makes sense. I tried it out:

❯ create table t as values (make_array([1], [2,3], [4])), (make_array([5], [6]));
0 rows in set. Query took 0.001 seconds.

❯ select * from t;
+--------------------+
| column1            |
+--------------------+
| [[1], [2, 3], [4]] |
| [[5], [6]]         |
+--------------------+
2 rows in set. Query took 0.001 seconds.

❯ select array_agg(column1) from t;
+----------------------------------+
| ARRAY_AGG(t.column1)             |
+----------------------------------+
| [[[1], [2, 3], [4]], [[5], [6]]] |
+----------------------------------+
1 row in set. Query took 0.004 seconds.

[haohuaijin]

I want to work on this issue.

[alamb]

This function currently works well

> select string_agg(v, ':') from (select 'foo'||value as v from generate_series(1,100));
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| string_agg(v,Utf8(":"))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| foo1:foo2:foo3:foo4:foo5:foo6:foo7:foo8:foo9:foo10:foo11:foo12:foo13:foo14:foo15:foo16:foo17:foo18:foo19:foo20:foo21:foo22:foo23:foo24:foo25:foo26:foo27:foo28:foo29:foo30:foo31:foo32:foo33:foo34:foo35:foo36:foo37:foo38:foo39:foo40:foo41:foo42:foo43:foo44:foo45:foo46:foo47:foo48:foo49:foo50:foo51:foo52:foo53:foo54:foo55:foo56:foo57:foo58:foo59:foo60:foo61:foo62:foo63:foo64:foo65:foo66:foo67:foo68:foo69:foo70:foo71:foo72:foo73:foo74:foo75:foo76:foo77:foo78:foo79:foo80:foo81:foo82:foo83:foo84:foo85:foo86:foo87:foo88:foo89:foo90:foo91:foo92:foo93:foo94:foo95:foo96:foo97:foo98:foo99:foo100 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row(s) fetched.
Elapsed 0.007 seconds.

So closing down