Hello,
I'm looking for a more efficient way of dynamically categorizing some
events. The following view definition looks into each event's latest
event_date object (a theater play can have several, a book only one) to
tell whether the event is current, past or future:
SELECT s.id_event_subtype, s.subtype, t.id_event_type, t.type,e.id_event, e.created_by, e.created_on, e.modified_by,
e.modified_on,e.id_image,e.show_name, e.length, d.id_date,d.start_date, d.end_date, d.low_price, d.high_price,
d.id_location,d.showtime, CASE WHEN d.start_date <= 'now'::text::date AND CASE
WHEN t.type = 'movie'::text THEN d.start_date >= ('now'::text::date - 21) WHEN t.type = 'book'::text
THENe.created_on >= ('now'::text::date - 28) ELSE d.end_date >= 'now'::text::date OR d.end_date IS NULL
END THEN '0_current'::text WHEN d.start_date > 'now'::text::date THEN '1_future'::text
WHENd.start_date IS NOT NULL THEN '2_past'::text ELSE ''::text END AS timingFROM event eNATURAL JOIN
event_type2tLEFT JOIN event_subtype2 s USING (id_event_subtype)LEFT JOIN show_date d USING (id_event);
This view is widely used in my application, including as a basis for
further views, as I almost always need to know the 'timing' category of
an event (past, current, future). But I have nagging doubts about its
efficiency. It also seems pretty slow in its current form.
Any suggestion on how to improve it (including schema modifications) are
more than welcome.
Thanks,
