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: 20080624084354.GA19752@apartia.fr (view raw or flat)
Thread:
Lists: pgsql-sql
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,

Responses

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-2014 The PostgreSQL Global Development Group