Is There Any Way to Generate a Standard "Diff" Between Two Strings in PostgreSQL?

Question

I'm trying to make a site like Wikipedia or Google Docs, with an audit history of every change made to certain tables. In other words, I want to set a trigger on table foo, so that every time the bar column changes, it adds a "diff" of the changes to a foo_audits table.

By "diff", I mean like the Linux tool diff, git diff, or any similar change tracker, where if the text "foo bar" was changed to "foo qux", the "diff" would be something like:

+ qux
- foo

(Except with the line/column numbers of the change added, using the standard diff format.)

Is there any way to generate such a diff in Postgresql?

P.S. A pure PostgreSQL solution would be nice, but I'd also be open to using another language. For instance, Node JS has a diff package that would do exactly what I want ... but I have no idea how to use it from inside a database trigger.

P.P.S. I found this code, for what looks like a diff function ... but it's not a free-standing "give me two strings and I'll give you a diff" function; it instead INSERTs the diff into a table. If anyone could help explain how I can extract the "diff" part of that script into its own function (sadly I'm not good at programming PostgreSQL), that would also solve things.

Basically I don't care how it works, I'm just looking for any solution that lets me diff inside the database ... and any help would be greatly appreciated.

I'm fine with Python, but not with using Python within PostgreSQL (or rather, I'm fine with that too ... I just don't know how to do it). — machineghost
– machineghost, Commented Sep 11, 2022 at 19:46
You can compile PostgreSQL from source using the --with-python option and/or you can install the plpythonu extension (u = untrusted). Never done it - will have to shortly, but if you're proficient in Python, might be worth a shot? — Vérace
– Vérace, Commented Sep 11, 2022 at 19:54

Jack Douglas · Accepted Answer · 2025-01-13 09:41:30Z

1

If you are happy with the output of Python's difflib (which is not the same as POSIX diff) you can use it like this:

create function diff (s1 text, s2 text) returns text as $$
  import difflib
  return ''.join(difflib.unified_diff(s1.splitlines(), s2.splitlines()))
$$ language plpython3u;

select diff ('foo bar','foo qux');

diff
--- +++ @@ -1 +1 @@ -foo bar+foo qux

fiddle

edited Jan 13 at 9:41

answered Jan 12 at 21:38

Jack Douglas

40.6k16 gold badges106 silver badges181 bronze badges

Add a comment |

Stack Exchange Network

Is There Any Way to Generate a Standard "Diff" Between Two Strings in PostgreSQL?

1 Answer 1

Your Answer

Hot Network Questions

Is There Any Way to Generate a Standard "Diff" Between Two Strings in PostgreSQL?

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions