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 On Sun, Aug 11, 2019 at 7:17 PM Debapriya Patra <[email protected]> wrote: > 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 > <http://public.deck_history.id>'* to *public.deck_history.id > <http://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. > > [image: Screen Shot 2019-08-11 at 8.51.17 AM.png] > > Any help would be greatly appreciated. > > > Thanks, > Deba > > -- > 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/jooq-user/14869172-f954-4b96-bbed-634b30d4fe32%40googlegroups.com > <https://groups.google.com/d/msgid/jooq-user/14869172-f954-4b96-bbed-634b30d4fe32%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO6QL-SpjJzwRuz8EzBSA25CTXS%3D1g%2Biit1bepbkUC-fAg%40mail.gmail.com.
