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 THEN
e.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
WHEN d.start_date IS NOT NULL THEN
'2_past'::text
ELSE ''::text
END AS timing
FROM event e
NATURAL JOIN event_type2 t
LEFT 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,
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
