diff-/patch-functionality for text-type data inside PostgreSQL

From: "Markus Wollny" <Markus(dot)Wollny(at)computec(dot)de>
To: <pgsql-general(at)postgresql(dot)org>
Subject: diff-/patch-functionality for text-type data inside PostgreSQL
Date: 2009-05-04 10:26:13
Message-ID: 28011CD60FB1724DBA4442E38277F6260C9C9DE4@hermes.computec.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi!

I want to implement a versioning system for text columns of a table
inside a PostgreSQL 8.3 database. As some of the changes to a text field
might be very small compared to the total text size, I'd prefer storing
diffs instead of full previous versions of the text and use a patch-like
function whenever I want to actually roll back to a certain version. I
know that I could probably handle this quite easily in the application
code, but I'd prefer some solution running on the database itself, so
that the application wouldn't have to know anything about storing the
diffs, instead that process would be handled by a ON UPDATE trigger.

So far I have been playing around with PL/PerlU for diff/path
functionality, using CPAN modules Text::Diff and Text::Patch, but
haven't been too successful, as there seems to be some issue with this
mechanism if the text data doesn't contain newlines. Just as an
off-topic info, because it's some issue with the CPAN modules, not with
PostgreSQL:

#!/usr/bin/perl
use Text::Patch;
use Text::Diff;
$src = "foo sdffasd";
$dst = "34asd sdf";
$diff = diff( \$src, \$dst, { STYLE => 'Unified' } );
print $diff . "\n";
$out = patch( $src, $diff, { STYLE => 'Unified' } );
print "Patch successful\n" if $out eq $dst;

Running this results in the following output:
@@ -1 +1 @@
-foo sdffasd+34asd sdf
Hunk #1 failed at line 1.

Anyway, has anybody already done something in this direction,
preferrably in some way that is purely pl/* and wouldn't require any
custom-made C-library? So far I have only found this interesting
description of the implementation of the very same functionality here:
http://www.ciselant.de/projects/pg_ci_diff/doc.html - but there's no
source code supplied for the libpg_ci_diff.so library.

Kind regards

Markus

Computec Media AG
Sitz der Gesellschaft und Registergericht: Furth (HRB 8818)
Vorstandsmitglieder: Albrecht Hengstenberg (Vorsitzender) und Rainer Rosenbusch
Vorsitzender des Aufsichtsrates: Jurg Marquard
Umsatzsteuer-Identifikationsnummer: DE 812 575 276

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jasen Betts 2009-05-04 10:53:38 Re: Calculating product from rows - (aggregate product )
Previous Message Allan Kamau 2009-05-04 10:09:53 Re: Calculating product from rows - (aggregate product ) [solution found, thanks Andreas]