On Thu, 27 Jan 2005 13:02:48 -0500, Merlin Moncure <[EMAIL PROTECTED]> wrote: > Alexandre wrote: > > On Thu, 27 Jan 2005 10:44:45 -0500, Merlin Moncure > > <[EMAIL PROTECTED]> wrote: > > > Alexandre wrote: > > > Let's start with the normalized result set. > > > > > > product_id | department_id | sum > > > 924 a 6000 > > > 924 c 1575 > > > 907 a 1500 > > > [...] > > > Right. I expanding departments into columns is basically a dead end. > First of all, SQL is not really designed to do this, and second of all > (comments continued below)
Ok, I got it. The basic message is to avoid making columns out of rows like I'm doing right now, that "de-normalizing" in an array is the way to go. So I should query and get the results in an array then after my application will parse the array into the good columns. (I'm developping a software.) If I still got it wrong, this is because the 'geek' section of my brain is in vacation: leave a message and when it'll come back, it'll explain all this to me! :) So I found the array_accum function in the doc, so I did create it. CREATE AGGREGATE array_accum ( sfunc = array_append, basetype = anyelement, stype = anyarray, initcond = '{}' ); Then I created this new select: SELECT product_id, array_accum(department_id) as a_department_id, array_accum(req_time) as a_req_time FROM (SELECT * FROM design.product_department_time) AS tmp GROUP BY product_id; It gives: product_id | a_department_id | a_req_time ------------+-----------------+------------------ 924 | {A,C} | {6000,1575} 907 | {A,C,D} | {1500,1500,4575} 906 | {A,C,D} | {3000,3000,1935} So, the performance should be much better using this agregate approach? No I thing I'll merge the results in my software, unless you think that at this point doing a LEFT JOIN with my jobs table is the way to go, beacuse the performance will be good. (Personally I don't know the answer of this one.) > If parsing an array string is a pain I happen to have a C++ class handy > that can compose/decompose a postgresql array string if: > a: no more than 1 dimension and > b: array bounds are known > > Let me know if you need it and I'll send it over. Thank you for your offer. I think parsing an array is the easiest thing to do for me in all this. :) If I encounter any problem, I'll drop you a mail. Regards. -- Alexandre Leclerc ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])