On duplicate key with hstore merge using jooq

Question

Currently, I'm trying to implement the following sql query in jooq:

INSERT INTO table as t (id, hstore_data) 
VALUES ('test', '"key1" => "val1"') 
ON CONFLICT (id) 
DO UPDATE SET hstore_data = add(t.hstore_data, '"keyX" => "valX"');

add() is a custom function:

CREATE FUNCTION add(hstore, hstore) RETURNS hstore
AS 'select $1 || $2;'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

So far, I managed to get this up and running:

return DSL.using(configuration)
            .insertInto(TABLE)
            .columns(TABLE.ID, TABLE.HSTORE_DATA)
            .values(table.getId(), table.getHstoreData())
            .onDuplicateKeyUpdate()
            .set(TABLE.HSTORE_DATA,
                            merge(
                                    DSL.using(configuration).select(TABLE.HSTORE_DATA).from(TABLE).where(TABLE.ID.eq(table.getId())).fetchAnyInto(HashMap.class)
                                    , table.getHstoreData()

                            )
                    )
            .execute();

merge() is a simple JAVA function merging two maps.

This approach works. However, I'd like to do all the processing on the database server as the first query suggests.

I tried to use the routine that jooq generates for add(). But it seems like jooq does not use the hstore binding. The binding is defined in the ConfigGenerator as follows:

types.add(new ForcedType()
            .withUserType("java.util.Map<String, String>")
            .withBinding("HStoreStringBinding")
            .withIncludeExpression(".*_data")
            .withIncludeTypes(".*"));
types.add(new ForcedType()
            .withUserType("java.util.Map<String, Long>")
            .withBinding("HStoreLongBinding")
            .withIncludeExpression(".*_counts")
            .withIncludeTypes(".*"));

The binding works for hstores, just not for the custom function.

1. Are there more clever approaches?
2. What might be the reason for add() not being correctly typed?
3. How can I tell jooq to use the original value in the merge as I do in the raw SQL query with t.hstore_data?

Answer 1

What might be the reason for add() not being correctly typed?

Try naming your function parameters, otherwise you cannot match them with the includeExpression property. To match the return value of the function, use the function name itself as an includeExpression. Of course, you could just leave the property away, and match all hstore types with a more generic binding:

types.add(new ForcedType()
            .withUserType("java.util.Map<String, String>")
            .withBinding("HStoreStringBinding")
            .withIncludeTypes("hstore"));

How can I tell jooq to use the original value in the merge as I do in the raw SQL query with t.hstore_data?

Once code generation works, you just use your add function, just like in the SQL version of your statement:

set(TABLE.HSTORE_DATA, Routines.add(TABLE.HSTORE_DATA, table.getHstoreData()))