1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
select (e.title, e.subtitle, e.description)::text what, e.serviceid, e.eventid, sv.transportstreamid,
e.starttime - max(s.early), e.stoptime - interval '1 second' + max(s.late),
max(s.priority)
from services sv, events e, schedules s
where (s.serviceid is null or s.serviceid = e.serviceid)
and (s.title is null or lower(s.title) = lower(e.title))
and (s.eventid is null or s.eventid = e.eventid)
and (s.search is null or event_tsvector(e) @@ plainto_tsquery(s.search))
and sv.serviceid = e.serviceid
and e.starttime > now()
and not exists (
select 1
from seen
where lower(e.title) = lower(seen.title)
and coalesce(lower(e.subtitle), '') = coalesce(lower(seen.subtitle), '')
and ts_rank(to_tsvector(e.description), plainto_tsquery(seen.description)) +
ts_rank(to_tsvector(seen.description), plainto_tsquery(e.description)) > 1)
group by e.serviceid, e.eventid, sv.serviceid
order by max(s.priority) desc, e.title, e.subtitle, e.description, sv.transportstreamid, e.starttime
|
