crosstab maybe by case statement - Mailing list pgsql-sql

Hi Folks,

I have one that I need your advise on how to construct.

I have the need to create a series of dates starting from a Sunday  - one week 
apart.

(select ((date_trunc('week', '2012-02-12'::date)::date) +(i+6)) as week_date 
from
generate_series(0,84,7) i) as foo

that gives a table of sunday dates starting from '2012-02-12'::date

The dates will be used in the column headings ( I don't know in advance what 
those dates will be - because the start sunday can change).

I have written functions to return sum()'s of a field for the week starting 
from the sundays returned from the above dates.

getqtyordered(itemsite_id, foo.week_date)  -- returns qty order for the week
getqtyalloc(itemsite_id, foo.week_date)  -- qty_alloc for the week
... I actually have several of these types of functions.

And of course there is other information retrieved.

What I need to output is the following:

Name            date 1     date2   date3    date4 ... 
qty order         10            8        20          15
qty_alloc         6              0        3             50

What I can't figure out is how to get the dates to become my field/column 
name.  

BELOW IS MY ACTUAL FUNCTION:

CREATE OR REPLACE FUNCTION xchromasun._chromasun_totals(date) RETURNS SETOF weekly_mpr AS
$BODY$
DECLARE
_passed_date alias for $1;_start_date date;
_warehous_id integer;
_firm_planned_accum numeric;
_running_avail numeric;
_start_qoh numeric;
_itemsite_id integer;
_olditemsite_id integer;
rec record;
myrecord weekly_mpr;
counter integer;
BEGIN



_running_avail :=0;
_firm_planned_accum := 0;
_olditemsite_id := 0;
_itemsite_id := 0;
counter := 0;
--find Sunday
select the_date from
(select (_passed_date::date) - num as the_date, extract(dow from 
((_passed_date::date) - num)) as weekday from (select generate_series(0,6) as num) as t) as myans where weekday = 0 
into _start_date;

for rec in select itemsite_id,item_number, item_descrip1, itemsite_qtyonhand, 
itemsite_safetystock,foo.week_date,itemsite_leadtime,warehous_code,
coalesce(vend_name,'NOT ON FILE') as "vendor", coalesce(vend_number, 'NONE') 
as "vend_number",
xchromasun._chromasun_getqtyordered(itemsite_id, foo.week_date) as 
"qty_ordered",
xchromasun._chromasun_getqtyallocated(itemsite_id, foo.week_date) as 
"qty_alloc",
xchromasun._chromasun_getqtypr(itemsite_id, foo.week_date) as "purch_req",
xchromasun._chromasun_getqtyplanneddemand(itemsite_id, foo.week_date) as 
"planned_demand",
qtyavailable(itemsite_id, foo.week_date) as "qty_avail",
(select xchromasun._chromasun_getqtyfirmed(itemsite_id, foo.week_date)) as 
"firm_planned_orders",
(xchromasun._chromasun_getqtyplanned(itemsite_id, foo.week_date) - (select 
xchromasun._chromasun_getqtyfirmed(itemsite_id, foo.week_date))) as 
"planned_orders"
from public.itemsite
left join whsinfo on (warehous_id = itemsite_warehous_id)
left join item on (item_id = itemsite_item_id)
left join itemsrc on (itemsrc_item_id = item_id)
left outer join vendinfo on (vend_id = itemsrc_vend_id),
(select ((date_trunc('week', _start_date::date)::date) +(i+6)) as week_date 
from
generate_series(0,84,7) i) as foo
where itemsite_item_id in (select item_id from item) and item_type = 'P'
order by item_number asc,vend_number,week_date

loopcounter := counter +1;
_olditemsite_id  :=rec.itemsite_id;
IF _itemsite_id <> _olditemsite_id THEN _itemsite_id := rec.itemsite_id; _running_avail := rec.itemsite_qtyonhand;END
IF;

_firm_planned_accum = _firm_planned_accum + rec.firm_planned_orders ;

_running_avail = _running_avail  - rec.planned_demand  + rec.qty_ordered -
rec.qty_alloc ;

myrecord.counter := counter::integer;
myrecord.warehous_code := rec.warehous_code;
myrecord.week_of := rec.week_date;
myrecord.qty_ordered := rec.qty_ordered;
myrecord.firm_planned_orders := rec.firm_planned_orders;
myrecord.planned_orders := rec.planned_orders;
myrecord.item_number := rec.item_number;
myrecord.item_descrip1 := rec.item_descrip1;
myrecord.itemsite_qtyonhand := rec.itemsite_qtyonhand;
myrecord.itemsite_safetystock := rec.itemsite_safetystock;
myrecord.qty_alloc := rec.qty_alloc;
myrecord.qty_avail := rec.qty_avail;
myrecord.planned_qoh := _running_avail;
myrecord.firm_avail := _firm_planned_accum;
myrecord.lead_time := rec.itemsite_leadtime;
myrecord.vend_number := rec.vend_number;
myrecord.vendor := rec.vendor;
myrecord.purch_req := rec.purch_req;
myrecord.planned_demand := -rec.planned_demand;

return next myrecord;


end loop;

return;

END;
$BODY$ LANGUAGE plpgsql VOLATILE COST 100 ROWS 1000;
ALTER FUNCTION xchromasun._chromasun_totals(date) OWNER TO postgres;