Re: Repetitive code
От | Aaron Bono |
---|---|
Тема | Re: Repetitive code |
Дата | |
Msg-id | bf05e51c0606160744m5f28243cjf0fd6da11f48404d@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Repetitive code (Joe <dev@freedomcircle.net>) |
Ответы |
Re: Repetitive code
(Joe <dev@freedomcircle.net>)
Re: Repetitive code (Greg Stark <gsstark@mit.edu>) |
Список | pgsql-sql |
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
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
On 6/16/06, Joe <dev@freedomcircle.net> wrote:
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.
В списке pgsql-sql по дате отправления:
Предыдущее
От: Geoffrey KnauthДата:
Сообщение: Re: listen_addresses = '*' ok, specific address(es) no