In this blog post Generating ranges with PostgreSQL I introduced generating ranges. As introductory post it was sufficient but what is bad about it that it has hard coded values i.e. it is not flexible. If we want to have reservation system we need to have flexible creation of intervals.
It can be done using PostgreSQL functions using plain SQL, only simple stuff for this example. When you want to get more result from functions you can have two constructs. You can return a set of values or a table. There is a small difference in syntax, usage is almost the same.
The code for the function with returning set is following:
create function generate_ts(beginning timestamp, finish timestamp, gap interval)
returns TABLE(reservation tsrange) as
$$
with gen(ts) as
(
select generate_series(beginning, finish, gap)
),
ss(start, stop) as (
select ts, lead(ts, 1) over () from gen
)
select tsrange(start,stop)
from ss
where stop > start;
$$ LANGUAGE SQL;
We call this function with following statement:
select * from generate_ts('2014-09-17 09:00:00'::timestamp,
'2014-09-17 16:00:00'::timestamp,
'1 hour'::interval);
And we will have following result:
reservation
-----------------------------------------------
["2014-09-17 09:00:00","2014-09-17 10:00:00")
["2014-09-17 10:00:00","2014-09-17 11:00:00")
["2014-09-17 11:00:00","2014-09-17 12:00:00")
["2014-09-17 12:00:00","2014-09-17 13:00:00")
["2014-09-17 13:00:00","2014-09-17 14:00:00")
["2014-09-17 14:00:00","2014-09-17 15:00:00")
["2014-09-17 15:00:00","2014-09-17 16:00:00")
(7 rows)
So now we have customized time range interval. And if we want we can automatically insert into a table that we will help us create reservation system.
Example for inserting these values into system is this:
select * into reservation_system
from (select reservation
from generate_ts('2014-09-17 09:00:00'::timestamp,
'2014-09-18 16:00:00'::timestamp,
'1 hour'::interval)) s;
This query will automatically create table from generate table.
So if we run query SELECT * FROM reservation_system we will have the same output as in our first call for the function generate_ts.
This is step further into creating reservation system using modern PostgreSQL.
