I've been trying to speed up a generic auditing trigger that I had written in pl/pgsql some time back. On update, it generates a list of columns in the table being updated and inserts rows in the auditing table recording any changes (by table, column, data before, data after, etc). The same trigger function is used across multiple tables.

I'm toying with pl/perl as it seems to be much faster for the task at hand, but I seem to have run into a problem on differentiating between NULL and empty string ('') value in the database.

In my mind if a column goes from NULL to an empty string (or vice versa), that's a change that I need to record. However, using the available new/old column references ($_TD->{new/old}{$columnName}), I can't seem to differentiate between columns that are actually null and those that contain the empty string. Columns that I know are null are being caught by both the empty and the undef checks, as are columns that I know are empty.

    if($_TD->{new}{$column} eq '') {
        elog(NOTICE, "New value in column $column is empty");
    }
    if($_TD->{old}{$column} eq '') {
        elog(NOTICE, "Old value in column $column is empty");
    }
    if($_TD->{new}{$column} eq undef) {
        elog(NOTICE, "New value in column $column is not defined");
    }
    if($_TD->{old}{$column} eq undef) {
        elog(NOTICE, "Old value in column $column is not defined");
    }

I suspect that I'm doing something silly here, but maybe I'm trying to do something that I simply can't do. Any advice?

Edit - Using Postgres 8.4.4 for what it's worth

Edit - After looking at filiprem's post below (and lots more testing), I ended up with this, which seems to be working:

    my %newrow = %{$_TD->{new}};
    my %oldrow = %{$_TD->{old}};

    my $valChanged;
    while (($column,$value) = each %newrow) {

        $valChanged = 0;

        if($newrow{$column} ne $oldrow{$column}) {
            $valChanged = 1;
            elog(NOTICE, "Values in column $column differ.  New: $_TD->{new}{$column}, Old: $_TD->{old}{$column}");
        }   
        elsif(!defined($newrow{$column}) && defined($oldrow{$column})) {
            elog(NOTICE, "New row contains nulled out field");
            $valChanged = 1;
        }
        elsif(defined($newrow{$column}) && !defined($oldrow{$column})) {
            elog(NOTICE, "New row contains newly populated field");
            $valChanged = 1;
        }

        if($valChanged) {
            ### Update audit table
        }
    }

It catches the difference between empty strings and NULLs and logs them all appropriately in the audit table.

up vote 4 down vote accepted

You did not specify PostgreSQL version. On 9.0.5 I have observed the same behavior (not sure if it's a bug, see comments below).

This is easy to workaround - you can first test for definedness to catch NULL, and if it passed, test for empty string.

if ( not defined $_TD->{ new }{ $column } ) {
    elog( NOTICE, "New value in column $column is not defined" );
}
elsif ( $_TD->{ new }{ $column } eq '' ) {
    elog( NOTICE, "New value in column $column is empty" );
}
if ( not defined $_TD->{ old }{ $column } ) {
    elog( NOTICE, "Old value in column $column is not defined" );
}
elsif ( $_TD->{ old }{ $column } eq '' ) {
    elog( NOTICE, "Old value in column $column is empty" );
}
  • This is not a bug because SQL null means 'NO VALUE' and an empty string IS a value. – xenoterracide Nov 2 '11 at 0:05
  • 1
    I had my Null vs empty string run-in a few years back, that part I've got; I'm actually trying to distinguish between the two situations here. My failure seems to be on the Perl side of things. After toying more, using the actual defined function and not comparing to the undef value seems to help. I also started extracting the rows from the trigger data (my %newrow = %{$_TD->{new}};) before looking at the columns. I'll provide more info when I know it works. – Jafoy Nov 2 '11 at 1:45
  • @xenoterracide, I think it's a bug in PL/Perl, I'm not discussing SQL NULL semantics here (good article, BTW). If it's not a bug then please say - How can PL/Perl distinguish NULL::text arg from ''::text arg, in a better way than I've shown? – filiprem Nov 2 '11 at 5:25
  • Led me to the proper result. – Jafoy Nov 2 '11 at 16:18

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.