agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedgenerate a range within a view
2+ messages / 1 participants
[nested] [flat]
* generate a range within a view
@ 2013-10-10 10:26 Gary Stainburn <gary.stainburn@ringways.co.uk>
2013-10-10 11:03 ` Re: generate a range within a view Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 2+ messages in thread
From: Gary Stainburn @ 2013-10-10 10:26 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: generate a range within a view
2013-10-10 10:26 generate a range within a view Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2013-10-10 11:03 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 2+ messages in thread
From: Gary Stainburn @ 2013-10-10 11:03 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2013-10-10 11:03 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-10-10 10:26 generate a range within a view Gary Stainburn <gary.stainburn@ringways.co.uk>
2013-10-10 11:03 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox