Re: Repetitive code

I haven't stared at your query as long as you have so I may have missed something but it looks like in all the selects you are combining the first column in the select is the column you filter on.  So the the outer query doesn't have to know wiether it is a new or changed row:

SELECT * FROM (
    SELECT created as my_date, topic_id, 0, 0, 0, 0 FROM topic
    WHERE page_type IN (1, 2)
  UNION
    SELECT updated as my_date, topic_id, 1, 0, 0, 0 FROM topic
    WHERE date_trunc('day', updated) != created
      AND page_type IN (1, 2)
  UNION
    SELECT e.created as my_date, subject_id, 0, 1, entry_id, subject_type
    FROM entry e, topic
    WHERE subject_id = topic_id AND page_type IN (1, 2)
  UNION
    SELECT e.created as my_date, actor_id, 0, 1, entry_id, actor_type
    FROM entry e, topic
    WHERE actor_id = topic_id  AND page_type IN (1, 2)
  UNION
    SELECT e.updated as my_date, 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 page_type IN (1, 2)
  UNION
    SELECT e.updated as my_date, 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 page_type IN (1, 2)
  UNION
    SELECT e.created as my_date, e.topic_id, 0, 1, entry_id, rel_type
    FROM topic_entry e, topic t
    WHERE e.topic_id = t.topic_id AND page_type IN (1, 2)
  UNION
    SELECT e.updated as my_date, 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 page_type IN (1, 2)
) my_union
where my_union.my_date >= $dt

I would almost be tempted to create a view for each small query and name them something meaningful and then another view that does the union.  It would make the queries easier to understand at least (self documented).

-Aaron