4Kp8id42

1How to loop through array_agg as iterated columns?
2order_id|customer_id|order_date  |order_desc
31       |1          |"2010-01-01"|"Tom's First" 
42       |1          |"2010-04-01"|"Tom's Second" 
57       |1          |"2010-04-13"|"Tom's Third" 
68       |1          |"2011-04-13"|"Tom's Last" 
75       |1          |"2011-06-20"|"Tom's Really Last." 
83       |2          |"2010-07-07"|"Dick's First" 
96       |2          |"2011-07-07"|"Dick's Other" 
104       |3          |"2011-04-04"|"Harry's Only"
11	
12select cu.customer, array_agg(ord.order_id) as orders from test_order ord
13inner join test_customer cu
14on ord.customer_id = cu.customer_id
15group by cu.customer
16	
17customer   |orders  
18"Tom"      |"{1,2,7,8,5}"  
19"Dick"     |"{3,6}"  
20"Harry"    |"{4}"
21	
22select cu.customer,   
23(array_agg(ord.order_id))[1] as order_1,  
24(array_agg(ord.order_id))[2] as order_2,  
25(array_agg(ord.order_id))[3] as order_3,  
26(array_agg(ord.order_id))[4] as order_4,  
27(array_agg(ord.order_id))[5] as order_5   
28from test_order ord  
29inner join test_customer cu  
30on ord.customer_id = cu.customer_id  
31group by cu.customer
32	
33customer|order_1|order_2|order_3|order_4|order_5  
34"Dick"  |3      |6      |       |       |  
35"Harry" |4      |       |       |       |   
36"Tom"   |8      |1      |5      |2      |7
37	
38customer|order_id1|order_date1|order_desc1|order_id2|order_date2|order_desc2| ...
39	
40CREATE FUNCTION loop_test(integer) RETURNS integer AS $$
41
42DECLARE
43rOrder RECORD;
44loop_counter INT := 1;
45target_customer_id ALIAS FOR $1;        
46BEGIN
47
48FOR rOrder IN SELECT * 
49    FROM vdad_data.test_order 
50    WHERE customer_id = target_customer_id 
51    ORDER BY order_id LOOP
52
53    IF NOT EXISTS
54        (
55        SELECT * FROM information_schema.COLUMNS
56        WHERE COLUMN_NAME= 'order_id' || loop_counter
57        AND TABLE_NAME='test_customer'
58        AND TABLE_SCHEMA='vdad_data'
59        )
60        THEN
61
62        EXECUTE 'ALTER TABLE vdad_data.test_customer
63        ADD COLUMN order_id' || loop_counter || ' integer';
64    END IF;
65
66    IF NOT EXISTS
67        (
68        SELECT * FROM information_schema.COLUMNS
69        WHERE COLUMN_NAME= 'order_date' || loop_counter
70        AND TABLE_NAME='test_customer'
71        AND TABLE_SCHEMA='vdad_data'
72        )
73        THEN
74
75        EXECUTE 'ALTER TABLE vdad_data.test_customer
76        ADD COLUMN order_date' || loop_counter || ' date';
77    END IF;
78
79
80    IF NOT EXISTS
81        (
82        SELECT * FROM information_schema.COLUMNS
83        WHERE COLUMN_NAME= 'order_desc' || loop_counter
84        AND TABLE_NAME='test_customer'
85        AND TABLE_SCHEMA='vdad_data'
86        )
87        THEN
88
89        EXECUTE 'ALTER TABLE vdad_data.test_customer
90        ADD COLUMN order_desc' || loop_counter || ' character varying';
91    END IF;
92
93EXECUTE 'UPDATE vdad_data.test_customer 
94      SET order_id' || loop_counter || ' = ' || rOrder.order_id ||',
95      order_date' || loop_counter || ' = ' || quote_literal(to_char(rOrder.order_date,'yyyy-mm-dd')) ||',         
96      order_desc' || loop_counter  || ' = ' ||  quote_literal(rOrder.order_desc) ||'
97      WHERE customer_id = ' ||rOrder.customer_id;
98
99loop_counter = loop_counter + 1;
100END LOOP;
101
102RETURN 1;
103END;
104$$ LANGUAGE plpgsql;
105	
106select cu.customer,
107       row_number() OVER(PARTITION BY cu.customer ORDER BY ord.order_date)
108from test_order ord inner join test_customer cu  
109  on ord.customer_id = cu.customer_id  
110group by cu.customer