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