Skip site navigation (1) Skip section navigation (2)

dynamic events categorization

From: Louis-David Mitterrand <vindex+lists-pgsql-sql(at)apartia(dot)org>
To: pgsql-sql(at)postgresql(dot)org
Subject: dynamic events categorization
Date: 2008-06-24 08:43:54
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-sql

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,,
				WHEN d.start_date <= 'now'::text::date AND 
					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.



pgsql-sql by date

Next:From: Pascal TufenkjiDate: 2008-06-24 14:33:11
Subject: ANSI Standard
Previous:From: Scott MarloweDate: 2008-06-21 03:43:24
Subject: Re: Cross Tab Functions

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group