Thread: Possibly odd question; diff results?

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).

   Has anyone done this or should I just look into writing a small
program to do this outside of postgres?

Thanks!

Madi

On 2/8/07, Madison Kelly <linux@alteeve.com> 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

Merlin Moncure wrote:
> On 2/8/07, Madison Kelly <linux@alteeve.com> 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