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.
[image: Screen Shot 2019-08-11 at 8.51.17 AM.png] <about:invalid#zClosurez>
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.
