[GENERAL] Possibly odd question; diff results?

[Madison Kelly]

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


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Possibly odd question; diff results?

[Merlin Moncure]

On 2/8/07, Madison Kelly <[EMAIL PROTECTED]> 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

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Possibly odd question; diff results?

[Madison Kelly]

Merlin Moncure wrote:

On 2/8/07, Madison Kelly <[EMAIL PROTECTED]> 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  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

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster