crosstab maybe by case statement

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

loop
counter := 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;