ordering of join using ON expression = any (array)

From: Gerhard Hintermayer <gerhard(dot)hintermayer(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: ordering of join using ON expression = any (array)
Date: 2011-05-17 08:06:22
Message-ID: BANLkTim-Vak=h8fQ8jd=Wqp7YQWCFyX=gQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

thanks for any input
Gerhard

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Craig Ringer 2011-05-17 09:18:46 Re: Memcached for Database server
Previous Message Robert Klemme 2011-05-17 07:00:31 Re: [PERFORMANCE] expanding to SAN: which portion best to move