Before answering your questions I would like to know if you have already looked at the PostgreSQL ARRAY_POSITION() function and the WITH ORDINALITY clause for the UNNEST() function. I believe they could be quite useful for your problem.
Knut On Sun, Aug 11, 2019, 18:13 Debapriya Patra <[email protected]> wrote: > 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*; > > *I tested my function with the below query in which works fine.* > > > > > > -- 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/CAFx%3DKgcra_4YUOfsUe7wsbj1HwF%3DR7OXZOJp62VzpGnmMR%2BLEg%40mail.gmail.com.
