See comments below.

On Sun, Aug 11, 2019 at 10:14 PM Debapriya Patra <debapriya.pa...@gmail.com>
wrote:

> 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.
> https://wiki.postgresql.org/wiki/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 ?
>

AFAICT your CUSTOM_ORDER() function does exactly what ARRAY_POSITION()
already does, but probably in a less efficient way.

What your original query did with UNNEST() and GENERATE_SERIES() would be
covered by the WITH ORDINALITY clause I mentioned, but probably more
efficient.

Which approach you take is of course up to you.

Hope this helps,
Knut


> Cheers,
> Debapriya Patra
> 650.933.6852
>
>
> On Sun, Aug 11, 2019 at 12:41 PM Knut Wannheden <knut.wannhe...@gmail.com>
> wrote:
>
>> 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 <debapriya.pa...@gmail.com>
>> 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 jooq-user+unsubscr...@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
>> <https://groups.google.com/d/msgid/jooq-user/CAFx%3DKgcra_4YUOfsUe7wsbj1HwF%3DR7OXZOJp62VzpGnmMR%2BLEg%40mail.gmail.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 jooq-user+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/jooq-user/CAM2NkrTt%2BE6cjUCRHLUp3qWHcnFooyRYEOsWOqc%2BKV5UPuxD-w%40mail.gmail.com
> <https://groups.google.com/d/msgid/jooq-user/CAM2NkrTt%2BE6cjUCRHLUp3qWHcnFooyRYEOsWOqc%2BKV5UPuxD-w%40mail.gmail.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 jooq-user+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/jooq-user/CAFx%3DKgdsb3BdO2zBnCt3iAd2fViLdRJ8e9%3DeN0T8kkK08nVJrA%40mail.gmail.com.

Reply via email to