CONVOLUTED CROSS-TAB ? UNION ? INTERSECT ? 16.9 second performance.... Is there a better way ???

Hey all....

I need to join two tables and retrieve a 75 X 15 column matrix for all the
records in table 1 across a 13 week range so the output should look like
this. I would like the missing records in Table 2 to populate the structure
with 0.00...

Week

Type Code
Description
14
15
16
17
18
19
20
21
22
23
24
25
26

AC1
Activity 1
0.0
0.0
1.35
2.10
2.56
3.12
4.00
5.00
12.10
17.50
21.90
25.00
45.50

AC1
Activity 2
2.00
1.34
1.35
2.10
2.56
8.00
8.00
12.11
0.00
0.00
0.00
5.00
4.00

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

GROUP TOTALS
2.00
1.34
2.70
4.20
5.12
11.12
12.00
17.11
12.10
17.50
21.90
30.00
49.5

B1
Billing 1

B2
Billing 2

GROUP TOTALS

The two table definitions:

CREATE TABLE l_activity_type
(
id serial NOT NULL PRIMARY KEY,
activity_group int4 NOT NULL DEFAULT 0,
type_code varchar(5) NOT NULL DEFAULT ''::character varying,
type_desc varchar(50) NOT NULL DEFAULT ''::character varying,
display_order int4 NOT NULL DEFAULT 0,
calc_order int4 NOT NULL DEFAULT 0,
create_dt timestamp NOT NULL DEFAULT now(),
change_dt timestamp NOT NULL DEFAULT now(),
active_flag bool DEFAULT true

) WITH OIDS;

l_activity_type has 75 rows

CREATE TABLE l_store_hours
(
id serial NOT NULL PRIMARY KEY,
l_store_id int4 NOT NULL DEFAULT 0,
l_activity_type int4 NOT NULL REFERENCES l_activity_type(id),
week_code int4 NOT NULL DEFAULT 0,
year_code int4 NOT NULL DEFAULT 0,
amount numeric(10,2) DEFAULT 0.00,
create_dt timestamp NOT NULL DEFAULT now(),
change_dt timestamp NOT NULL DEFAULT now(),
change_id int4 DEFAULT 0,
active_flag bool DEFAULT true

) WITH OIDS;

l_store_hours has 2,131,250 rows approximately...

So far -- I have written the following function which returns a type of
"storehours":

DROP TYPE storehours CASCADE;
CREATE TYPE storehours AS (

activity_group INTEGER,
type_code VARCHAR(50),
display_order INTEGER,
type_desc VARCHAR(50),
week_code INTEGER,
amount NUMERIC(10,2)

);

-- DROP FUNCTION f_storehours();
CREATE OR REPLACE FUNCTION f_storehours(integer, integer, integer, integer)
RETURNS SETOF storehours AS '

DECLARE STORE ALIAS FOR $1;
DECLARE START ALIAS FOR $2;
DECLARE STOP ALIAS FOR $3;
DECLARE YEAR ALIAS FOR $4;
DECLARE ROW RECORD;
DECLARE WEEK INTEGER;

BEGIN

FOR WEEK IN START .. STOP LOOP

FOR ROW IN SELECT * FROM (SELECT LT.activity_group, LT.type_code,
LT.display_order, LT.type_desc,
WEEK as week_code, 0.00 AS amount FROM l_activity_type LT

UNION ALL

SELECT LT.activity_group, LT.type_code, LT.display_order, LT.type_desc,
LH.week_code,
LH.amount FROM l_activity_type LT
JOIN l_store_hours LH ON LT.id = LH.l_activity_type AND LH.l_store_id =
STORE
AND LH.week_code = WEEK AND LH.year_code = YEAR) AS A LOOP

RETURN NEXT ROW;

END LOOP;

END LOOP;
RETURN;

END;

' LANGUAGE PLPGSQL;

SELECT * FROM f_storehours(365, 14, 26, 2006) ORDER BY activity_group,
display_order, type_code, week_code;

EXPLAIN ANALYZE yields the following:

Sort (cost=62.33..64.83 rows=1000 width=211) (actual
time=16726.014..16726.286 rows=1618 loops=1)
Sort Key: activity_group, display_order, week_code

Regards,

Greg Patnude - Manager, Dynamic Applications Group

Data Mosaics, Inc.

2406 South Dishman-Mica Road / Suite # 6

Spokane Valley, WA 99206-6429

VOICE: (866) 904-DMSF

FAX: (509) 928-4236

-> Function Scan on f_storehours (cost=0.00..12.50 rows=1000 width=211)
(actual time=16720.839..16722.757 rows=1618 loops=1)
Total runtime: 16726.841 ms
(4 rows)