From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: generate a range within a view
Date: Thu, 10 Oct 2013 11:26:50 +0100
User-Agent: KMail/1.9.10
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <201310101126.50974.gary.stainburn@ringways.co.uk>
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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 int4 not 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 not null,
  day_limit   int4 not null CHECK (day_limit >= 0),
  primary key (s_id,de_id, de_date),
  foreign key (s_id, de_id) references site_user_departments (s_id, de_id)
);


-- 
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