with
cta1 as (
select array[
'125d7988-14d7-4898-bf89-0b54eb8aa317',
'63028718-bf33-4e3f-a6bd-ef7c0e941205',
'a1266416-4e7d-4426-9b9d-e5c66d15ffbc',
'940b3c50-7a5e-49d5-8105-b3ddfdfde9c1',
'86a1647f-a24d-45c0-ae04-0e19c2fd10e2'] as a),
cta2 as (
select
unnest(a)::uuid id,
generate_series(1, array_length(a, 1)) idx
from cta1)
select
idx,
d.id,
d.title
from cta2
join public.deck d on d.id = cta2.id order by idx;
String[] values = new String[] { "125d7988-14d7-4898-bf89-0b54eb8aa317", "63028718-bf33-4e3f-a6bd-ef7c0e941205" };ctx.with("cta1").as(select(array(values).as("a"))).with("cta2").as(select(function("unnest", String.class, field("a")).cast(SQLDataType.UUID).as("id"),function("generate_series", Integer.class, val(1), function("array_length", Integer.class, field("a"), val(1))).as("idx")).from("cta1")).select(field("id"), field("idx")).from("cta2").fetch();
--
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/7bcd822f-1d43-4d1b-8cb3-351138d0e682%40googlegroups.com.
Thanks for the reply. Looks like this is causing a performance issue So
planned to change to a user defined function and call that from JOOQ.
*1. I am defining my own function now in postgres:*
*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*;
*I tested my function with the below query in which works fine.*
*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)
*2. Trying to call the same function from my repository using JOOQ:*
UUID[] deckIdArray = deckIds.stream().toArray(UUID[]::new);
Field<Integer> field = customOrder(deckIdArray, DECK_HISTORY.ID);
SelectSeekStep1<DeckHistoryRecord, Integer> deckHistoryRecords =
dsl.selectFrom(DECK_HISTORY).where(DECK_HISTORY.ID.in(deckIds)).orderBy(field);
ResultSet rs = deckHistoryRecords.fetchResultSet();
*3. Trying to hit the service to get the response from DB using this code,
its giving me the error below.*
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)
*And when i tried to pull the query generated by JOOQ, it looks like 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"'
)
Can you please help me figuring out like whats going wrong here ? I see a
difference with the query generated by JOOQ *'"public"."deck_history"."id"'
*which should have been *"public"."deck_history"."id". Becasue when i
change to **"public"."deck_history"."id", *i am able to run that query
against DB successfully. So some . mistake i am doing with that function
argument while passing.
It will . be great if you can help me figuring that out.
*Also one more issue i see is, the function is generated with
deprecated not sure why? I am attaching the screenshot for that.*
*Thanks a lot for your solution and quick response. Its really great.*
[image: Screen Shot 2019-08-11 at 8.51.17 AM.png]
*Thanks,*
*Deba*
Hi Kunt,Thanks for the reply. Looks like this is causing a performance issue So planned to change to a user defined function and call that from JOOQ.
1. I am defining my own function now in postgres:
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;
--
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/CAFx%3DKgcra_4YUOfsUe7wsbj1HwF%3DR7OXZOJp62VzpGnmMR%2BLEg%40mail.gmail.com.
Nope i have not tried. Basically i wanted a way to sort the data by the input order i am sending to DB. So i thought of using this array index.Why i likes this is, its basically not doing anything on the DB just sorting with the array index which will be performant i believe.Do you think using PostgreSQL ARRAY_POSITION() function and the WITH ORDINALITY clause for the UNNEST() function will better than array index ?
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAM2NkrTt%2BE6cjUCRHLUp3qWHcnFooyRYEOsWOqc%2BKV5UPuxD-w%40mail.gmail.com.