Merlin Moncure wrote:
On 2/8/07, Madison Kelly <linux@xxxxxxxxxxx> wrote:
Hi all,
I've got a 'history' schema that records changes in the public schema
tables over time. I use a trigger and function to do this. What I would
like to do though, and this may not even be possible, is say something
like (pseudo-code) "SELECT DIFF foo_name FROM history.foo WHERE
foo_id=X;" and have a *nix 'diff' style results shown (sort of like
looking at diffs in CVS/SVN).
you can start by using the 'except' boolean query operator;
select * from foo except * from bar; This will give you rows in foo
that are not exactly in bar (matching every field). If you want it in
both directions you can:
(select * from foo except select * from bar)
union
(select * from bar except select * from foo);
you can then take the results of these queries and mark up the text
however you want. Just a heads up: the boolean sql operators are
famous for generating sequential scans.
merlin
Hi, Thanks for replying! I think this isn't much help for me though. For
example;
SELECT att_id, att_name, att_pt_id FROM history.attribute WHERE att_id=3;
att_id | att_name | att_pt_id
--------+-------------------------------+----------------------
3 | Bay, Internal 3.5" drive | 44,7,8,1,26,39,40,41
3 | Bay, Internal 3.5" drive | 44,7,8,1,26,36
3 | Bay, Internal 3.5" drive | 44,7,8,1,26,36
SELECT att_id, att_name, att_pt_id FROM attribute WHERE att_id=3;
att_id | att_name | att_pt_id
--------+-------------------------------+----------------
3 | Bay, Internal 3.5" drive | 44,7,8,1,26,36
SELECT att_id, att_name, att_pt_id FROM history.attribute WHERE att_id=3
EXCEPT SELECT att_id, att_name, att_pt_id FROM attribute WHERE att_id=3;
att_id | att_name | att_pt_id
--------+--------------------------+----------------------
3 | Bay, Internal 3.5" drive | 44,7,8,1,26,36
3 | Bay, Internal 3.5" drive | 44,7,8,1,26,39,40,41
This shows me the rows in the history schema that are not in the
public schema, which is nice but it doesn't tell me which columns have
changed in each version. What I would like would be results like (pseudo
again):
SELECT <DIFF> history_id, att_id, att_name, att_pt_id FROM
history.attribute WHERE att_id=3 ORDER BY modified_date DESC;
history_id | att_id | att_name | att_pt_id
------------+--------+-------------------------------+----------------------
86 | | Bay, Internal 3.5" drive |
85 | | | 44,7,8,1,26,36
82 | 3 | Bay, Internal 3.5" drive | 44,7,8,1,26,39,40,41
The first row being all new so all items return, the second row
returns only the 'att_pt_id' which changed, and the third returning
'att_name' which changed.
Thanks again!!
Madi
