From: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
To: Gordon McVey <grj.mcvey@googlemail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Extract created and last modified data
Date: Thu, 19 Aug 2010 14:24:48 +0100
Message-ID: <07AF2300D72042C4BF9FC9D897CEFC15@marktestcr.marktest.pt> (raw)
References: <AANLkTi=W7vwAFxKEkFMHvm-hteNCBi1eJ5vYJA2qWDn0@mail.gmail.com>
Howdy, Gordon.
This query is an attempt to replicate your items table with data just from changelog table.
Could you please try it out?
Best,
Oliveiros
SELECT itm_id,usr_id_create, itm_date_create, usr_id_modify, itm_date_modify
FROM
(
SELECT a.itm_id, a.usr_id as usr_id_create, create as itm_date_create
FROM changelog a
JOIN
(SELECT MIN(log_timestamp) as create, itm_id FROM changelog GROUP BY itm_id) b
ON (a.itm_id = b.itm_id
AND log_timestamp = create)
) subquery1
NATURAL JOIN
(
SELECT c.itm_id, usr_id as usr_id_modify, last_modified as itm_date_modify
FROM changelog c
JOIN (SELECT MAX(log_timestamp) as last_modified, itm_id FROM changelog GROUP BY itm_id) d
ON (d.itm_id = c.itm_id
AND c.log_timestamp = last_modified)
) subquery2
I have a CMS with a table of items, and another table serving as a log
of operations performed on the items. The revelent table structure is
as follows:
items
itm_id | usr_id_create | itm_date_create | usr_id_modify |
itm_date_modify | .....
itm_id is a serial primary key.
usr_id_* are the keys of the users who created and last modified the
item.
item_date_* are timestamps for creation and last modification times.
changelog
itm_id | usr_id | log_timestamp | log_op
itm_id and usr_id are foreign keys into the respective item and user
tables. log_timestamp is when the last change was made and log_op was
the operation performed.
.
Can anybody help with writing the query to get a view that replicates
the items table except with the create and last modified user IDs and
timestamps loaded from the changelog table? If there is anybody who
has actually implemented something similar, what kind of performance
impact would it have?
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: oliveiros.cristina@marktest.pt, grj.mcvey@googlemail.com
Subject: Re: Extract created and last modified data
In-Reply-To: <07AF2300D72042C4BF9FC9D897CEFC15@marktestcr.marktest.pt>
* 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