CODEWITHSUNDEEP
sqlpostgresql
Tomas Walch
Tomas Walch

Reputation: 2305

Filter SQL query on the generated results

I have a plpgsql function that generates TSTZRANGEs between two dates according to stored rules in a table called timeslots. It is working fine but now I got a new requirement and added a TSTZRANGE field called datespan to the timeslots table that I also need to filter against, so that only ranges contained within this field's value are returned.

Showing you here part of the function I need to change. eid, fromdate and todate are parameters passed in to the function.

SELECT TSTZRANGE(
    (series::DATE + ts1.start)::TIMESTAMPTZ AT TIME ZONE 'UTC',
    (series::DATE + ts1.end)::TIMESTAMPTZ AT TIME ZONE 'UTC'
) AS range
FROM
  timeslots AS ts1,
  generate_series(fromdate, todate, '1 day') AS series
WHERE
  eid = ts1.event_id AND
  EXTRACT(DOW FROM series) = ANY(ts1.weekdays)
ORDER BY
  range

So what I want to do is add a filter like WHERE range <@ ts1.datespan but I can't manage to put it anywhere that works.

EXAMPLE: Given this information in timeslots table:

 id |  start   |   end    |   weekdays    | event_id |           datespan 
----+----------+----------+---------------+----------------------------------------------------------------
 1  | 12:00:00 | 14:00:00 | {1,2,3,5,6,0} |       1  | ["2017-01-01 00:00:00+00","2017-02-01 00:00:00+00")
 2  | 09:00:00 | 11:00:00 | {1,2,3,5,6,0} |       1  | [null, null)
 3  | 15:00:00 | 17:00:00 | {1,2,3,5,6,0} |       1  | ["2017-02-01 00:00:00+00","2017-03-01 00:00:00+00")

The result of calling the SELECT above if eid=1, fromdate='2017-02-02' and todate='2017-02-03' should look like this:

                        range                        
-----------------------------------------------------
 ["2017-02-02 09:00:00+00","2017-02-02 11:00:00+00")
 ["2017-02-02 15:00:00+00","2017-02-02 17:00:00+00")
 ["2017-02-03 09:00:00+00","2017-02-03 11:00:00+00")
 ["2017-02-03 15:00:00+00","2017-02-03 17:00:00+00")

Upvotes: 1

Views: 116

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125254

The easiest is to use lateral:

The LATERAL key word can precede a sub-SELECT FROM item. This allows the sub-SELECT to refer to columns of FROM items that appear before it in the FROM list. (Without LATERAL, each sub-SELECT is evaluated independently and so cannot cross-reference any other FROM item.)

select ts1.*, range
from
    timeslots as ts1,
    generate_series('2017-02-02'::date, '2017-02-03', '1 day') as series
    cross join lateral
    (
        select tstzrange (
            (series::date + ts1.start)::timestamptz at time zone 'utc',
            (series::date + ts1.end)::timestamptz at time zone 'utc'
        ) as range
    ) range
where
    1 = ts1.event_id and
    extract(dow from series) = any(ts1.weekdays) and
    range <@ ts1.datespan
order by range
;
 id |  start   |   end    |   weekdays    | event_id |                      datespan                       |                        range                        
----+----------+----------+---------------+----------+-----------------------------------------------------+-----------------------------------------------------
  2 | 09:00:00 | 11:00:00 | {1,2,3,5,6,0} |        1 | (,)                                                 | ["2017-02-03 09:00:00+00","2017-02-03 11:00:00+00")
  3 | 15:00:00 | 17:00:00 | {1,2,3,5,6,0} |        1 | ["2017-02-01 00:00:00+00","2017-03-01 00:00:00+00") | ["2017-02-03 15:00:00+00","2017-02-03 17:00:00+00")

The alternative would be to wrap it in an outer query. Now you need to decide/inform what to do with the null boundaries.

Upvotes: 1

Related Questions