Date: Thu, 15 Jun 2006 21:34:31 -0400
From: Joe <dev@freedomcircle.net>
Subject: Repetitive code
To: pgsql-sql@postgresql.org
Message-id: <44920AA7.7070709@freedomcircle.net>
Organization: Freedom Circle, LLC
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7bit
User-Agent: Thunderbird 1.5.0.4 (Windows/20060516)

Hi,

This is prompted by the previous thread on "SQL Technique Question".  I 
have the following query, extracted from a PHP script, where $dt is a 
date provided to the script.

     SELECT created, topic_id, 0, 0, 0, 0 FROM topic
     WHERE created >= $dt AND page_type IN (1, 2)
   UNION
     SELECT updated, topic_id, 1, 0, 0, 0 FROM topic
     WHERE date_trunc('day', updated) != created
       AND updated >= $dt AND page_type IN (1, 2)
   UNION
     SELECT e.created, subject_id, 0, 1, entry_id, subject_type
     FROM entry e, topic
     WHERE subject_id = topic_id AND e.created >= $dt
       AND page_type IN (1, 2)
   UNION
     SELECT e.created, actor_id, 0, 1, entry_id, actor_type
     FROM entry e, topic
     WHERE actor_id = topic_id AND e.created >= $dt
       AND page_type IN (1, 2)
   UNION
     SELECT e.updated, subject_id, 1, 1, entry_id, subject_type
     FROM entry e, topic
     WHERE date_trunc('day', e.updated) != e.created
       AND subject_id = topic_id AND e.updated >= $dt
       AND page_type IN (1, 2)
   UNION
     SELECT e.updated, actor_id, 1, 1, entry_id, actor_type
     FROM entry e, topic
     WHERE date_trunc('day', e.updated) != e.created
       AND actor_id = topic_id AND e.updated >= $dt
       AND page_type IN (1, 2)
   UNION
     SELECT e.created, e.topic_id, 0, 1, entry_id, rel_type
     FROM topic_entry e, topic t
     WHERE e.topic_id = t.topic_id AND e.created >= $dt
       AND page_type IN (1, 2)
   UNION
     SELECT e.updated, e.topic_id, 1, 1, entry_id, rel_type
     FROM topic_entry e, topic t
     WHERE e.topic_id = t.topic_id
       AND date_trunc('day', e.updated) != e.created
       AND e.updated >= $dt AND page_type IN (1, 2);

As you can see, there's quite a bit of repetitive code, so the previous 
thread got me to thinking about simplifying it, perhaps through a view, 
perhaps through the use of CASE statements, particularly since I'm about 
to add at least one other table to the mix.

As background, each table has a 'created' date column and an 'updated' 
timestamp column and the purpose of the various selects is to find the 
rows that were created or updated since the given $dt date.  The third 
expression in each select list is an indicator of NEW (0) or CHANGED 
(1).  The fourth item is a code for row type (topic=0, entry=1, but a 
new code is coming).

I've been trying to figure out if simplifying into a view (one or more) 
is indeed possible.  One factoring out that I can see is the "topics of 
interest" restriction (i.e., the join of each secondary table back to 
topic to get only topics whose page_types are 1 or 2).  Another 
redundancy is the "date_trunc('day', updated) != created" which is there 
to avoid selecting "changed" records when they're actually new. 
However, although creating these views may simplify the subqueries it 
doesn't seem there is a way to avoid the eight-way UNION, or is there?

TIA

Joe