From: Joe <dev@freedomcircle.net>
To: pgsql-sql@postgresql.org
Subject: Repetitive code
Date: Thu, 15 Jun 2006 21:34:31 -0400
Message-ID: <44920AA7.7070709@freedomcircle.net> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@freedomcircle.net
Subject: Re: Repetitive code
In-Reply-To: <44920AA7.7070709@freedomcircle.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox