How to use array_remove for UDT arrays in JOOQ

[Pragya Gaur]

I have a TYPE defined say customtype and have a table where one of the columns is customtype[]. 

I am trying to find ways where I can execute the below sql query in JOOQ:

update MY_TABLE SET customtypecolumn = array_remove(customtypecolumn, ('test3','-1')::customtypecolumn) where <some_condition>

I tried following this link https://jooq-user.narkive.com/ScEfUAlX/fastest-way-to-remove-values-from-an-integer-array

But this way I am getting exception TYPE customtype is not supported in dialect POSTGRES

[Lukas Eder]

ARRAY_REMOVE will be supported in the upcoming jOOQ 3.18:

https://github.com/jOOQ/jOOQ/issues/11981

Until then, you can just use plain SQL templating whenever you need to work with vendor specific functions that aren't supported:

https://www.jooq.org/doc/latest/manual/sql-building/plain-sql-templating/

Field<CustomTypeRecord> f = DSL.val(new CustomTypeRecord("test3", "-1"));

DSL.field("array_remove({0}, {1})", f.getDataType(), MY_TABLE.CUSTOMTYPECOLUMN, f);

I hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/78e86317-41d1-41ed-ba6d-f2060ac3018an%40googlegroups.com.

[Pragya Gaur]

I am very new to JOOQ. Can you please suggest how to use it in update statements. I tried out something like this but not working for me.

Field<CustomTypeRecord> f = DSL.val(new CustomTypeRecord("test3", "-1"));

dslContext.update()
.set( Tables.MY_TABLE.CUSTOMTYPE,
DSL.field("array_remove({0}, {1})", f.getDataType(), Tables.MY_TABLE.CUSTOMTYPE, f))

.where(condition)
.execute();

[Lukas Eder]

Can you please be specific about this "something"?

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/2fcbcc56-c404-4b5e-b765-fad5a6757c04n%40googlegroups.com.

[Pragya Gaur]

I meant, I tried below step, but it is not working for me.

Field<CustomTypeRecord> f = DSL.val(new CustomTypeRecord("test3", "-1"));

dslContext.update()
.set( Tables.MY_TABLE.CUSTOMTYPE, DSL.field("array_remove({0}, {1})", f.getDataType(), Tables.MY_TABLE.CUSTOMTYPE, f))

.where(condition)
.execute();

[Lukas Eder]

How exactly is it not working for you? What are you seeing? Runtime errors? Wrong results?

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/72e9a543-9ac1-4a52-a23f-1853dc9342ben%40googlegroups.com.

[Pragya Gaur]

I am getting complier error "cast arguments to RowN on the set statement. (set( Tables.MY_TABLE.CUSTOMTYPE, DSL.field("array_remove({0}, {1})", f.getDataType(), Tables.MY_TABLE.CUSTOMTYPE, f)))

And if cast both the set functions params to RowN then it complies but then I get runtime error that cannot convert field to row. Can you please suggest how to use this DSL.field("array_remove({0}, {1})", f.getDataType(), Tables.MY_TABLE.CUSTOMTYPE, f) in update set method.

Field<CustomTypeRecord> f = DSL.val(new CustomTypeRecord("test3", "-1"));

dslContext.update(Tables.MY_TABLE)

.set( Tables.MY_TABLE.CUSTOMTYPE, DSL.field("array_remove({0}, {1})", f.getDataType(), Tables.MY_TABLE.CUSTOMTYPE, f))

.where(condition)
.execute();

[Lukas Eder]

Pragya,

You probably simplified your own example a bit, to the extent that the code shared here doesn't reflect the actual code or the actual problem. In particular, I'm a bit confused about your usage of CUSTOMTYPE vs CUSTOMTYPECOLUMN (a type and a field aren't the same thing)

Can you please verify that you're using the correct references of fields etc.? Or, rather, share the actual, complete code example here?

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/c3f56a46-18e3-4503-a8b4-a95df394c940n%40googlegroups.com.

[Pragya Gaur]

I have created a type as below:

CREATE TYPE schema.custom_type AS (
name VARCHAR(255),
lastname   VARCHAR(255),
is_old BOOLEAN,
propagate BOOLEAN);

And I have a table that has an array of custom_type as shown below:

CREATE TABLE IF NOT EXISTS schema.entity (
entity_id VARCHAR(255),

temp_id VARCHAR(255),
associations schema.custom_type[],

PRIMARY KEY (entity_id, temp_id)

);

Now I have a requirement to execute the below query in jooq: 

update schema.entity SET associations = array_remove(associations, ('name1','lastname1',true,true)::associations) where entity_id = '85f74dc4-ab9f-46b5-8fce-1591b9b24b03';

I tried below but its giving me complier error to cast arguments to RowN and if I do that then at runtime I get error saying that there is no data type custom_type in postgres.

Field<CustomTypeRecord> f = DSL.val(new CustomTypeRecord("name1", "lastname1",true, true));

dslContext.update(Tables.ENTITY)
.set( Tables.ENTITY.ASSOCIATIONS, DSL.field("array_remove({0}, {1})", f.getDataType(), Tables.ENTITY.ASSOCIATIONS, f))

.where(Tables.ENTITY.ENTITY_ID.eq("85f74dc4-ab9f-46b5-8fce-1591b9b24b03")

.execute();

Can you please help me construct this.

[Lukas Eder]

Ah yes, my bad. The data type isn't f.getDataType() (which is DataType<CustomTypeRecord>), but  ENTITY.ASSOCIATIONS.getDataType() (which is DataType<CustomTypeRecord[]>)

As so often with type inference in Java and other languages, try to assign things to local variables, so you'll see what is expected / inferred.

I hope this helps

Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/7367dd6f-f5fd-44db-a6e6-add34452e87dn%40googlegroups.com.

[Pragya Gaur]

I tried the above but now I am getting the below error:

org.jooq.exception.DataAccessException: SQL [update "schema"."entity" set "associations" = array_remove("schema"."entity"."associations", row(?, ?, ?, ?)) where "schema"."entity"."entity_id" = cast(? as uuid)]; ERROR: function array_remove(schema.custom_type[], record) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 53
    at org.jooq_3.14.16.POSTGRES.debug(Unknown Source)
    at org.jooq.impl.Tools.translate(Tools.java:2903)
    at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:757)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:389)
    at org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:119)

Any idea on this.

[Lukas Eder]

And what if you're trying this on the latest version of jOOQ

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/4cbf6c1b-e7c8-4fbb-b805-8900774b2fban%40googlegroups.com.

[Pragya Gaur]

Why this utility is not available in 3.14.16 jooq version?
or am I still doing something wrong here since it says function array_remove(schema.custom_type[], record) does not exist. Are the function params correct?

[Lukas Eder]

There's a missing cast in the generated SQL on your side. A related bug was fixed in jOOQ 3.17.0, 3.16.5, and 3.15.9:

https://github.com/jOOQ/jOOQ/issues/13174

I hope this helps,

Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/bcb29744-5b31-48e4-99ad-75f183f4b1f8n%40googlegroups.com.

[Lukas Eder]

For the record, here's what SQL is being generated in jOOQ 3.18.0-SNAPSHOT:

update "public"."t_entity"

set "associations" = array_remove("public"."t_entity"."associations", cast(row('name1', 'lastname1', true, true) as "public"."u_custom_type"))

where "public"."t_entity"."entity_id" = '85f74dc4-ab9f-46b5-8fce-1591b9b24b03'

Please note the cast to "u_custom_type"

[Pragya Gaur]

Ok so I have to upgrade to at least 3.17 jooq version?

[Lukas Eder]

Well, as I said. The bug was fixed in jOOQ 3.17.0, 3.16.5, and 3.15.9

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/20763bb9-28de-429f-b519-226617e6542en%40googlegroups.com.

[Pragya Gaur]

Thanks a lot. It worked with 3.15.9 version.