Re: Repetitive code

Aaron Bono wrote:
> Each of your queries has the filter xxx >= $dt where the xxx is the 
> first column in each select.  You could simplify the query by turning 
> the unioned selects into a sub-query and then putting the $dt filter in 
> the outer query.

It would probably have to be two subqueries unless I can find a way to 
merge the differences between new and changed rows.

> I don't know if this will cause performance problems though.  If 
> PostgreSQL completes the inner query before filtering by your $dt you 
> may be better off leaving the $dt filters where they are.

The query is only run a few times a week so performance is largely not a 
concern.  I'm trying to simplify it to make adding tables less 
cumbersome (as a separate effort, the schema may be modified to 
normalize it, e.g., topic joins to entry via subject_id and actor_id and 
a subject and actor can also appear in topic_entry's topic_id).

> I know Oracle has materialized views.  Does PostgreSQL also have 
> materialized views?  If so, you could get great performance from your 
> views AND simplify your SQL.

AFAIK PostgreSQL does not support materialized views but it's 
interesting that you mention that because in essence the query is used 
to materialize a view, i.e., it's part of an INSERT / SELECT into a 
table which is then joined back to the other tables to construct a web 
page as well as an RSS feed.

Joe