Postgresql aggregate function string_agg with H2

[Laurent Wang Stemmer]

Hello, 

I am trying to test a repository using the specific Postgresql aggregate function string_agg.

I am using H2, but I cannot figure out how to make this world on both world: Postgresql and H2.

Do you have any idea how I can mimic this aggregate function string_agg with H2 without making my test irrelevant ?

Thank you in advance.

PS : I use the version 1.3.174 of h2

[Noel Grandin]

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

[Fred&Dani&Pandora&Aquiles]

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

--
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 h2-database+unsubscribe@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Noel Grandin]

HI

Ah I see what you mean, no user-defined functions will not work for this case.

I have no problem with including this function, but I think you should verify that the syntax for string_agg is the same
as PostgreSQL, not a mix of H2 and PostgreSQL.

Also don't forget to write a couple of unit tests for it.

Thanks, Noel.

On 2015-04-01 01:24 PM, 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 <noelg...@gmail.com <mailto:noelg...@gmail.com>>:

>
>
>
> 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

> <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

> h2-database+unsubscribe@__googlegroups.com <mailto:h2-database%2Bunsu...@googlegroups.com>.
> To post to this group, send email to h2-da...@googlegroups.com <mailto:h2-da...@googlegroups.com>.
> Visit this group at http://groups.google.com/__group/h2-database <http://groups.google.com/group/h2-database>.
> For more options, visit https://groups.google.com/d/__optout <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

> h2-database...@googlegroups.com <mailto:h2-database...@googlegroups.com>.
> To post to this group, send email to h2-da...@googlegroups.com <mailto:h2-da...@googlegroups.com>.

[Rami Ojares]

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 <noelg...@gmail.com>:

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 h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
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 h2-database...@googlegroups.com.

[Fred&Dani&Pandora&Aquiles]

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

[Fred&Dani&Pandora&Aquiles]

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

[Noel Grandin]

Thank you very much, I have committed this patch!