Re: Possibly odd question; diff results? - Mailing list pgsql-general

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