Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

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.
–
JafoyNov 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?
–
filipremNov 2 '11 at 5:25