Aaron Bono wrote:
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
[snip]
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
Thanks Aaron. That does look like a great solution, overlooked since
I'm not that familiar with SELECTs in the FROM clause. It may even make
it possible to discard the interim table and do the web page/RSS feed
directly from the view.
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).
That sounds like a good idea too because schema changes would be
somewhat insulated by the layered views.
Best regards,
Joe
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
