Обсуждение: generate a range within a view

I have two tables, one defining a standard week by user department, the other 
defining a calendar where specific dates can deviate from the standard. The 
tables are shown below.

I'm trying to generate a view where I can do

select * from user_department_daily_limits where de_date >= '2013-10-06' and 
de_date <= '2013-10-12'

and it will generate 7 records using the deviation table for records that 
exist or the standard week where it doesn't.

I'm working on the idea that I will actually have to use a date range 
generator functoin to actually drive the view but I still can't get my head 
round it. Because I'm forced to work on Postgresql 8.3.3 I've had to write my 
own date_range function.

The best I can come up with is the following select but I can't work out how 
to convert it to a view.


select s.s_id, s.de_id, v.date,v.day_of_week, 
coalesce(l.day_limit,s.day_limit,0) as day_limit from (    select date_range as date, extract(DOW from date_range) as
day_of_week
 
from date_range('2013-10-06'::date,'2013-10-12'::date)
) as v
left outer join site_user_department_standard_week s on s.day_of_week = 
v.day_of_week
left outer join site_user_department_date_limit l on s.s_id = l.s_id and 
s.de_id = l.de_id and v.date = l.de_date;


Gary

create table site_user_department_standard_week ( s_id        char not null, de_id       int4 not null, day_of_week
int4not null CHECK (day_of_week >= 0 and day_of_week <= 6), day_limit   int4 not null CHECK (day_limit >= 0), primary
key(s_id,de_id, day_of_week), foreign key (s_id, de_id) references site_user_departments (s_id, de_id)
 
);

-- user_department_date_limit
-- defines records by user department / date to override the
-- standard week

create table site_user_department_date_limit ( s_id        char not null, de_id       int4 not null, de_date     date
notnull, day_limit   int4 not null CHECK (day_limit >= 0), primary key (s_id,de_id, de_date), foreign key (s_id, de_id)
referencessite_user_departments (s_id, de_id)
 
);




-- 
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk 

I've managed to do it using a function, shown below, but is there a better 
way?


create type site_user_department_limits as (s_id char, de_id int4, date date, 
day_id_week int4, day_limit);

create  or replace function site_user_department_limits(date,date)  returns setof site_user_department_limits as '
select s.s_id, s.de_id, v.date,v.day_of_week::int4, 
coalesce(l.day_limit,s.day_limit,0)::int4 as day_limit from (    select date_range as date, extract(DOW from
date_range)as day_of_week 
 
from date_range($1,$2)
) as v
left outer join site_user_department_standard_week s on s.day_of_week = 
v.day_of_week
left outer join site_user_department_date_limit l on s.s_id = l.s_id and 
s.de_id = l.de_id and v.date = l.de_date
'
language sql;

goole=# select * from site_user_department_limits('2013-10-06','2013-10-12');s_id | de_id |    date    | day_id_week |
day_limit
 
------+-------+------------+-------------+-----------H    |    80 | 2013-10-06 |           0 |         0H    |    80 |
2013-10-07|           1 |         5H    |    80 | 2013-10-08 |           2 |         5H    |    80 | 2013-10-09 |
   3 |         5H    |    80 | 2013-10-10 |           4 |         8H    |    80 | 2013-10-11 |           5 |         3H
  |    80 | 2013-10-12 |           6 |         2
 
(7 rows)

goole=# 

-- 
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk