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.
# This patch file was generated by NetBeans IDE
# Following Index: paths are relative to: /home/fred/NetBeansProjects/h2/trunk/h2/src/main/org/h2
# 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: command/Parser.java
--- command/Parser.java Base (BASE)
+++ command/Parser.java Modificado Localmente (Baseado em LOCAL)
@@ -2300,7 +2300,8 @@
read("BY");
agg.setGroupConcatOrder(parseSimpleOrderList());
}
- if (readIf("SEPARATOR")) {
+ // PostgreSQL compatibility: string_agg(expression, delimiter)
+ if (readIf("SEPARATOR") || readIf(",")) {
agg.setGroupConcatSeparator(readExpression());
}
r = agg;
Index: expression/Aggregate.java
--- expression/Aggregate.java Base (BASE)
+++ 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);
