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

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