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 | 0
H | 80 | 2013-10-07 | 1 | 5
H | 80 | 2013-10-08 | 2 | 5
H | 80 | 2013-10-09 | 3 | 5
H | 80 | 2013-10-10 | 4 | 8
H | 80 | 2013-10-11 | 5 | 3
H | 80 | 2013-10-12 | 6 | 2
(7 rows)
goole=#
--
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
