Error while calling user defined function from JOOQ

[Debapriya Patra]

Hi Kunt,

I have written a function in postgres DB.

CREATE OR REPLACE FUNCTION public.custom_order(anyarray, anyelement)

RETURNS INT AS

$$

  SELECT i FROM (

     SELECT generate_series(array_lower($1,1),array_upper($1,1))

  ) g(i)

  WHERE $1[i] = $2

  LIMIT 1;

$$ LANGUAGE SQL IMMUTABLE;

This is tested writing a query:

SELECT *

FROM public.deck_history dh

where id in(

'c02615e9-2ff4-47a0-ab56-103e1be7a20a',

'ae0b0756-8d00-47c9-aa74-a73a57f5e021',

'58ef7c63-3473-43c4-9e84-42e1b337c7d7',

'e9c8f273-5010-41f9-8ad1-eb5f3d661bbc',

'7d4a9263-21ea-4427-8498-12ea5f992157',

'1ec3cd36-514b-4684-920d-8cb23cea4e4e',

'a11fe2f2-07a2-4cb4-baf1-adf37206720f',

'3cd7458f-dd1d-4d34-9bea-0c1664b4b571',

'db692534-2bad-4503-b1f2-030c684a6c37',

'907875c2-00cc-47e9-bc89-1eda3055ae25'

)

ORDER BY custom_order(array['c02615e9-2ff4-47a0-ab56-103e1be7a20a',

'ae0b0756-8d00-47c9-aa74-a73a57f5e021',

'58ef7c63-3473-43c4-9e84-42e1b337c7d7',

'e9c8f273-5010-41f9-8ad1-eb5f3d661bbc',

'7d4a9263-21ea-4427-8498-12ea5f992157',

'1ec3cd36-514b-4684-920d-8cb23cea4e4e',

'a11fe2f2-07a2-4cb4-baf1-adf37206720f',

'3cd7458f-dd1d-4d34-9bea-0c1664b4b571',

'db692534-2bad-4503-b1f2-030c684a6c37',

'907875c2-00cc-47e9-bc89-1eda3055ae25']::uuid[], dh.id)

Now when i am trying to call that same function from my Java code using JOOQ, its giving me an error.

Java Code:

Field<Integer> field = customOrder(deckIdArray, DECK_HISTORY.ID);

SelectSeekStep1<DeckHistoryRecord, Integer> deckHistoryRecords = dsl.selectFrom(DECK_HISTORY).where(DECK_HISTORY.ID.in(deckIds)).orderBy(field);

RestltSet rs = deckHistoryRecords.fetchResultSet();

Error I am getting

org.jooq.exception.SQLDialectNotSupportedException: Type class org.jooq.impl.TableFieldImpl is not supported in dialect POSTGRES

  at org.jooq_3.11.8.POSTGRES.debug(Unknown Source)

  at org.jooq.impl.DefaultDataType.getDataType(DefaultDataType.java:884)

  at org.jooq.impl.DefaultDataType.getDataType(DefaultDataType.java:823)

  at org.jooq.impl.DefaultBinding$AbstractBinding.sqlCast(DefaultBinding.java:618)

  at org.jooq.impl.DefaultBinding$AbstractBinding.sql(DefaultBinding.java:707)

When i tried to pull the SQL generated by JOOQ, its below:

select 

  public.deck_history.id, 

  public.deck_history.foreign_id, 

  public.deck_history.title, 

  public.deck_history.deck_type, 

  public.deck_history.confidential, 

  public.deck_history.certified, 

  public.deck_history.deck_id, 

  public.deck_history.created, 

  public.deck_history.updated, 

  public.deck_history.original_created, 

  public.deck_history.original_updated, 

  public.deck_history.num_cards, 

  public.deck_history.num_images, 

  public.deck_history.edition, 

  public.deck_history.deleted

from public.deck_history

where public.deck_history.id in (

  'c02615e9-2ff4-47a0-ab56-103e1be7a20a', 'ae0b0756-8d00-47c9-aa74-a73a57f5e021', 

  '58ef7c63-3473-43c4-9e84-42e1b337c7d7', 'e9c8f273-5010-41f9-8ad1-eb5f3d661bbc', 

  '7d4a9263-21ea-4427-8498-12ea5f992157', '1ec3cd36-514b-4684-920d-8cb23cea4e4e', 

  'a11fe2f2-07a2-4cb4-baf1-adf37206720f', '3cd7458f-dd1d-4d34-9bea-0c1664b4b571', 

  'db692534-2bad-4503-b1f2-030c684a6c37', '907875c2-00cc-47e9-bc89-1eda3055ae25'

)

order by public.custom_order(

  cast('{c02615e9-2ff4-47a0-ab56-103e1be7a20a,ae0b0756-8d00-47c9-aa74-a73a57f5e021,58ef7c63-3473-43c4-9e84-42e1b337c7d7,e9c8f273-5010-41f9-8ad1-eb5f3d661bbc,7d4a9263-21ea-4427-8498-12ea5f992157,1ec3cd36-514b-4684-920d-8cb23cea4e4e,a11fe2f2-07a2-4cb4-baf1-adf37206720f,3cd7458f-dd1d-4d34-9bea-0c1664b4b571,db692534-2bad-4503-b1f2-030c684a6c37,907875c2-00cc-47e9-bc89-1eda3055ae25}' as uuid[]), 

  'public.deck_history.id'

)

And i tried running this query directly against DB, its breaking as well. But if i changes the second argument of the query from 'public.deck_history.id' to public.deck_history.id, it works fine for me. That means its not forming the right query and sendign the second argument as a string which is not right.

Can you please help me finding where i am doing wrong here ?

Also the code generated for the function is also deprecated in the Routines. Why is that. I am attaching the screenshot of that like where it shows deprecated.

Any help would be greatly appreciated.

Thanks,

Deba 

[Lukas Eder]

Hi Deba,

jOOQ currently doesn't support PostgreSQL's various "any" data types, including "anyarray" and "anyelement". You can implement two workarounds:

1) Overload your function with actually supported data types, which can then internally redirect to your existing function. E.g. custom_order(int[], int), custom_order(bigint[], bigint), custom_order(uuid[], uuid), etc. These can then be picked up by the jOOQ code generator and you can use them instead

2) Implement your custom data type binding for "anyarray" and "anyelement" and bind those to your function parameters: https://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings/

We'll be happy to provide further help with your next steps.

Cheers,

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/14869172-f954-4b96-bbed-634b30d4fe32%40googlegroups.com.