Re: ordering of join using ON expression = any (array)

On Tue, May 17, 2011 at 3:06 AM, Gerhard Hintermayer
<gerhard(dot)hintermayer(at)gmail(dot)com> wrote:
> Hi,
> is there a way to sort the joined tuples in the way they are in a the joined
> array ? BTW I'm using 8.4 (_I_ know I should upgrade, but management thinks
> we shouldn't :-( )
> I'd like to join 2 tables based on a column, where the column is an array in
> one table, but I still need to keep the order of tuples as they were
> originally in the array.
>
> What I now get is e.g. if the array contains A,B,C , I get rows B, C and A,
> but I'd like to get one row containing A, the B, then C
>
> My query is:
> select * from produkt inner join (select a_nr,komp as p_code from r_mi_sfm
> where a_nr=20110) as auftrag on (produkt.p_code = any(auftrag.p_code));
>
> and explain says:
>  Nested Loop  (cost=201.83..2656.51 rows=26992 width=98)
>    Join Filter: ("inner".p_code = ANY ("outer".komp))
>    ->  Index Scan using idx_r_mi_sfm_a_nr on r_mi_sfm  (cost=0.00..25.39
> rows=7 width=58)
>          Index Cond: (a_nr = 20110)
>    ->  Materialize  (cost=201.83..278.95 rows=7712 width=40)
>          ->  Seq Scan on produkt  (cost=0.00..194.12 rows=7712 width=40)

your best best is to not use the 'any' construct but to expand the
array with the index position which you can feed back into the query
w/order by. In 8.1, there is an undocumented function which you can
use to do this: information_schema._pg_expandarray(). It works more
or less like unnest, but also returns the index position.

select * from produkt inner join
(
select pg_expandarray(a_nr,komp) as v
from r_mi_sfm
where a_nr=20110
) as auftrag on produkt.p_code = (auftrag).v.x
order by (auftrag).v.n;

give it a shot -- if it doesn't work quite right let me know and i'll fix it.

merlin