Generating ranges using functions

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.

Generating ranges in PostgreSQL

There are great features using PostgreSQL RDBMS, and that are common table expressions (CTE), windowing function, and time ranges. There are interesting inbuilt function for testing purposes and this generates_series(). It supports generating integers, big integers, timestamps with and without time zone but it doesn't support ranges. As I have been learning about this great database I realized that it lack support for generating ranges. This is only core part of functions that will be build for generating ranges in PostgreSQL

with gen(ts) as 

(

select generate_series('2014-09-17'::timestamp,

                        '2015-01-07'::timestamp, 

                        '2 week'::interval)

),

ss(start, stop) as (

select ts, lead(ts, 1) over () from gen

)

select tsrange(start,stop) as "Genijalno" 

from ss 

where stop > start;

The result of the expression above is:

                  Genijalno

-----------------------------------------------

 ["2014-09-17 00:00:00","2014-10-01 00:00:00")

 ["2014-10-01 00:00:00","2014-10-15 00:00:00")

 ["2014-10-15 00:00:00","2014-10-29 00:00:00")

 ["2014-10-29 00:00:00","2014-11-12 00:00:00")

 ["2014-11-12 00:00:00","2014-11-26 00:00:00")

 ["2014-11-26 00:00:00","2014-12-10 00:00:00")

 ["2014-12-10 00:00:00","2014-12-24 00:00:00")

 ["2014-12-24 00:00:00","2015-01-07 00:00:00")

(8 rows)