title: How to loop through array_agg as iterated columns?
body: <p>Using PostgreSQL 8.4, I have successfully been able to use array_agg() to take a case of multiple orders and make a single row per customers:</p>
  
  <p>From this:</p>
  
  <pre><code>order_id|customer_id|order_date  |order_desc
  1       |1          |"2010-01-01"|"Tom's First" 
  2       |1          |"2010-04-01"|"Tom's Second" 
  7       |1          |"2010-04-13"|"Tom's Third" 
  8       |1          |"2011-04-13"|"Tom's Last" 
  5       |1          |"2011-06-20"|"Tom's Really Last." 
  3       |2          |"2010-07-07"|"Dick's First" 
  6       |2          |"2011-07-07"|"Dick's Other" 
  4       |3          |"2011-04-04"|"Harry's Only"
  </code></pre>
  
  <p>Using this:</p>
  
  <pre><code>select cu.customer, array_agg(ord.order_id) as orders from test_order ord
  inner join test_customer cu
  on ord.customer_id = cu.customer_id
  group by cu.customer
  </code></pre>
  
  <p>results in:</p>
  
  <pre><code>customer   |orders  
  "Tom"      |"{1,2,7,8,5}"  
  "Dick"     |"{3,6}"  
  "Harry"    |"{4}"  
  </code></pre>
  
  <p>And I can grab pieces of the array to create new columns if I hard code each iteration:</p>
  
  <pre><code>select cu.customer,   
  (array_agg(ord.order_id))[1] as order_1,  
  (array_agg(ord.order_id))[2] as order_2,  
  (array_agg(ord.order_id))[3] as order_3,  
  (array_agg(ord.order_id))[4] as order_4,  
  (array_agg(ord.order_id))[5] as order_5   
  from test_order ord  
  inner join test_customer cu  
  on ord.customer_id = cu.customer_id  
  group by cu.customer  
  </code></pre>
  
  <p>results in:</p>
  
  <pre><code>customer|order_1|order_2|order_3|order_4|order_5  
  "Dick"  |3      |6      |       |       |  
  "Harry" |4      |       |       |       |   
  "Tom"   |8      |1      |5      |2      |7  
  </code></pre>
  
  <p>However, what I'd like to do, in two steps:</p>
  
  <ol>
  <li><p>For Loop my way through the records so that I don't have to create every iteration of the field.  The good news is that the structure above doesn't error and just passes NULL, but if I ever get to some insane number of records, I don't have to have to keep creating order_55, order_56 etc manually in my statement.</p></li>
  <li><p>Even better would be eventually to not pass it a specific field and have it iterate through all the fields (barring the customer_id) and give me iterations of each field, to the effect of:</p>
  
  <pre><code>customer|order_id1|order_date1|order_desc1|order_id2|order_date2|order_desc2| ... 
  </code></pre>
  
  <p>etc etc.  Basically joining the parent table (customer) to the child (order), but having multiple child records go across a single row instead of creating multiples.  </p>
  
  <p>(Yes, I understand that this goes against the basic concept of why you do parent/child tables in the first place.  However, I pass this on to a client and this would make the process infinitely easier.)</p></li>
  </ol>
  
  <p><strong>UPDATE</strong>: I've gotten closer with the cannibalized function ... the first time I call it, it creates the columns and populates one of the customers.  But for some reason, my IF NOT EXISTS works when ran separately, but not within the function: I get a "column order_id1 exists" error.  I'd also like to eventually modify this so the specific fields aren't hard coded; instead of the customer_id I'd like to do something like pass the parent table, child table and joining ID, and have it fully append the child table in this crosstab-ish manner.</p>
  
  <pre><code>CREATE FUNCTION loop_test(integer) RETURNS integer AS $$
  
  DECLARE
  rOrder RECORD;
  loop_counter INT := 1;
  target_customer_id ALIAS FOR $1;        
  BEGIN
  
  FOR rOrder IN SELECT * 
      FROM vdad_data.test_order 
      WHERE customer_id = target_customer_id 
      ORDER BY order_id LOOP
  
      IF NOT EXISTS
          (
          SELECT * FROM information_schema.COLUMNS
          WHERE COLUMN_NAME= 'order_id' || loop_counter
          AND TABLE_NAME='test_customer'
          AND TABLE_SCHEMA='vdad_data'
          )
          THEN
  
          EXECUTE 'ALTER TABLE vdad_data.test_customer
          ADD COLUMN order_id' || loop_counter || ' integer';
      END IF;
  
      IF NOT EXISTS
          (
          SELECT * FROM information_schema.COLUMNS
          WHERE COLUMN_NAME= 'order_date' || loop_counter
          AND TABLE_NAME='test_customer'
          AND TABLE_SCHEMA='vdad_data'
          )
          THEN
  
          EXECUTE 'ALTER TABLE vdad_data.test_customer
          ADD COLUMN order_date' || loop_counter || ' date';
      END IF;
  
  
      IF NOT EXISTS
          (
          SELECT * FROM information_schema.COLUMNS
          WHERE COLUMN_NAME= 'order_desc' || loop_counter
          AND TABLE_NAME='test_customer'
          AND TABLE_SCHEMA='vdad_data'
          )
          THEN
  
          EXECUTE 'ALTER TABLE vdad_data.test_customer
          ADD COLUMN order_desc' || loop_counter || ' character varying';
      END IF;
  
  EXECUTE 'UPDATE vdad_data.test_customer 
        SET order_id' || loop_counter || ' = ' || rOrder.order_id ||',
        order_date' || loop_counter || ' = ' || quote_literal(to_char(rOrder.order_date,'yyyy-mm-dd')) ||',         
        order_desc' || loop_counter  || ' = ' ||  quote_literal(rOrder.order_desc) ||'
        WHERE customer_id = ' ||rOrder.customer_id;
  
  loop_counter = loop_counter + 1;
  END LOOP;
  
  RETURN 1;
  END;
  $$ LANGUAGE plpgsql;
  </code></pre>
  
  <p>I apologize for being all over the map, as I've been trying to tackle several things about this at once that I can't quite get.  Any help is appreciated, thanks!</p>