agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: Re: generate a range within a view
Date: Thu, 10 Oct 2013 12:03:33 +0100
Message-ID: <201310101203.33488.gary.stainburn@ringways.co.uk> (raw)
In-Reply-To: <201310101126.50974.gary.stainburn@ringways.co.uk>
References: <201310101126.50974.gary.stainburn@ringways.co.uk>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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
view thread (2+ messages)
Message-ID: <201310101203.33488.gary.stainburn@ringways.co.uk>
Permalink: ../201310101203.33488.gary.stainburn@ringways.co.uk/
Also on: postgresql.org/message-id/201310101203.33488.gary.stainburn@ringways.co.uk
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk
Subject: Re: generate a range within a view
In-Reply-To: <201310101203.33488.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox