Hi Noel,
As H2 already supports many features for compatibility with Postgres, I
thought that would be beneficial, a built in support for string_agg
aggregate function. In relation to the tests, the only place I found where
tests with group_concat are executed, was in TestScript.java. So, I
modified the file testScript.sql to include the related evaluation.
Well, let me know what you think about the patch.
Regards,
Fred
2015-04-01 11:32 GMT-03:00 Fred&Dani&Pandora&Aquiles <[email protected]>:
> Hi Rami,
>
> Yes, I think this is better solution for the related issue. Noel, are you
> interested to support the aggregate function string_agg, even with the
> solution proposed by Rami?
>
> Regards,
>
> Fred
>
> 2015-04-01 10:20 GMT-03:00 Rami Ojares <[email protected]>:
>
> You can implement user defined aggregate functions by implementing
>> org.h2.api.AggregateFunction
>> I have been using it and it works fine.
>> See: http://h2database.com/javadoc/index.html
>>
>> - rami
>>
>>
>> On 1.4.2015 14:24, Fred&Dani&Pandora&Aquiles wrote:
>>
>> Hi Noel,
>>
>> The string_agg is aggregate function similar to group_concat. The user
>> defined functions can be applied in this case?
>>
>> As I wasn't sure about this, I started to work in a simple patch to
>> support the string_agg in Postgres style. To keep simplicity, I just added
>> the possibility of comma as delimiter and the string_agg aggregate with the
>> group_concat type. However, I noted that is possible to mix the syntaxes of
>> H2 and Postgres which could lead to some errors like: select
>> string_agg(cola order by colb,colc,'/'). In the previous example, the
>> default comma delimiter will be used because the delimiter '/' will be
>> treated as a 'column' during
>> agg.setGroupConcatOrder(parseSimpleOrderList()). In cases where the
>> string_agg be wrote as expected in Postgres, the aggregation will work.
>>
>> Finally, If you considerer beneficial the inclusion of the new
>> aggregate function, do you think would be valid to ensure the correct
>> syntaxe of Postgres aggregate?
>>
>> Regards,
>>
>> Fred
>>
>> 2015-04-01 4:28 GMT-03:00 Noel Grandin <[email protected]>:
>>
>>>
>>>
>>> On 2015-03-31 03:15 PM, Laurent Wang Stemmer wrote:
>>>
>>>>
>>>> Do you have any idea how I can mimic this aggregate function string_agg
>>>> with H2 without making my test irrelevant ?
>>>>
>>>>
>>> http://h2database.com/html/features.html#user_defined_functions
>>>
>>>
>>> --
>>> You received this message because you are subscribed to the Google
>>> Groups "H2 Database" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to [email protected].
>>> To post to this group, send email to [email protected].
>>> Visit this group at http://groups.google.com/group/h2-database.
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "H2 Database" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected].
>> To post to this group, send email to [email protected].
>> Visit this group at http://groups.google.com/group/h2-database.
>> For more options, visit https://groups.google.com/d/optout.
>>
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "H2 Database" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected].
>> To post to this group, send email to [email protected].
>> Visit this group at http://groups.google.com/group/h2-database.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
# This patch file was generated by NetBeans IDE
# Following Index: paths are relative to: /home/fred/Dropbox/h2-trunk/trunk/h2/src/main
# This patch can be applied using context Tools: Patch action on respective folder.
# It uses platform neutral UTF-8 encoding and \n newlines.
# Above lines and this line are ignored by the patching process.
Index: org/h2/command/Parser.java
--- org/h2/command/Parser.java Base (BASE)
+++ org/h2/command/Parser.java Modificado Localmente (Baseado em LOCAL)
@@ -2270,7 +2270,7 @@
}
}
- private Expression readAggregate(int aggregateType) {
+ private Expression readAggregate(int aggregateType, String name) {
if (currentSelect == null) {
throw getSyntaxError();
}
@@ -2293,16 +2293,26 @@
}
}
} else if (aggregateType == Aggregate.GROUP_CONCAT) {
+ Aggregate agg = null;
+ if (equalsToken("GROUP_CONCAT", name)){
boolean distinct = readIf("DISTINCT");
- Aggregate agg = new Aggregate(Aggregate.GROUP_CONCAT,
+ agg = new Aggregate(Aggregate.GROUP_CONCAT,
readExpression(), currentSelect, distinct);
if (readIf("ORDER")) {
read("BY");
agg.setGroupConcatOrder(parseSimpleOrderList());
}
+
if (readIf("SEPARATOR")) {
agg.setGroupConcatSeparator(readExpression());
}
+ } else if (equalsToken("STRING_AGG", name)){
+ // PostgreSQL compatibility: string_agg(expression, delimiter)
+ agg = new Aggregate(Aggregate.GROUP_CONCAT,
+ readExpression(), currentSelect, false);
+ read(",");
+ agg.setGroupConcatSeparator(readExpression());
+ }
r = agg;
} else {
boolean distinct = readIf("DISTINCT");
@@ -2382,7 +2392,7 @@
}
int agg = getAggregateType(name);
if (agg >= 0) {
- return readAggregate(agg);
+ return readAggregate(agg, name);
}
Function function = Function.getFunction(database, name);
if (function == null) {
Index: org/h2/expression/Aggregate.java
--- org/h2/expression/Aggregate.java Base (BASE)
+++ org/h2/expression/Aggregate.java Modificado Localmente (Baseado em LOCAL)
@@ -163,6 +163,8 @@
addAggregate("MAX", MAX);
addAggregate("AVG", AVG);
addAggregate("GROUP_CONCAT", GROUP_CONCAT);
+ // PostgreSQL compatibility: string_agg(expression, delimiter)
+ addAggregate("STRING_AGG", GROUP_CONCAT);
addAggregate("STDDEV_SAMP", STDDEV_SAMP);
addAggregate("STDDEV", STDDEV_SAMP);
addAggregate("STDDEV_POP", STDDEV_POP);
# This patch file was generated by NetBeans IDE
# Following Index: paths are relative to: /home/fred/Dropbox/h2-trunk/trunk/h2/src/test
# This patch can be applied using context Tools: Patch action on respective folder.
# It uses platform neutral UTF-8 encoding and \n newlines.
# Above lines and this line are ignored by the patching process.
Index: org/h2/test/testScript.sql
--- org/h2/test/testScript.sql Base (BASE)
+++ org/h2/test/testScript.sql Modificado Localmente (Baseado em LOCAL)
@@ -8188,6 +8188,12 @@
> 1,2,3,4,5,6,7,8,9
> rows (ordered): 1
+SELECT STRING_AGG(ID,';') FROM TEST;
+> GROUP_CONCAT(ID SEPARATOR ';')
+> ------------------------------
+> 1;2;3;4;5;6;7;8;9
+> rows: 1
+
SELECT DISTINCT NAME FROM TEST;
> NAME
> --------
