8.3 PL/pgSQL comparing arbitrary records - Mailing list pgsql-general

Hi all,

Short version of my question:
What's the best way to compare arbitrary records (OLD and NEW, in my
case) using PL/pgSQL in Postgres 8.3, without knowing anything about
the structure of the records? If the answer is to cast OLD and NEW to
text, and then compare, as suggested in [1], what false equalities
could be produced, assuming the records have identical structure and
NULLs are allowed?

Long version:
I am porting a trigger function written in PL/Python to PL/pgSQL. The
function, called track_updates() is declared as a BEFORE INSERT OR
UPDATE trigger on many tables in our database; simplifying a bit, its
job is to bump up an "updated" timestamp whenever a row has been
modified.

This simple plpython snippet tests whether the old and new rows are
identical (i.e. an update with no effect), and ignores if so:

if TD["new"] == TD["old"]:
    return "SKIP"

I'd like to perform a similar test in plpgsql, but the straightforward:

IF NEW IS NOT DISTINCT FROM OLD THEN
    RETURN NEW;

doesn't work -- I get:

    ERROR:  operator does not exist: [my table name] = [my table name]
    LINE 1: SELECT   $1  IS NOT DISTINCT FROM  $2
    HINT:  No operator matches the given name and argument type(s).
You might need to add explicit type casts.

According to discussion[1] ("8.3 PLpgSQL Can't Compare Records?") a
few months ago, the workaround for < 8.4 is to cast OLD and NEW to
text, and then compare the two text values. A comment there suggests
that comparing NULL and the empty string in this way might incorrectly
result in a true equality test, which is a little worrying for my
purposes. However, I'm unable to reproduce NULL and '' equating to
each other when cast to text (see example code below). Are there any
false equalities or other gotchas I should be worried about when
comparing OLD::text and NEW::text? I can safely assume for my purposes
that the old and new records will have the same structure (i.e. no
ALTER TABLEs to worry about).

I'm using Postgres 8.3.4, compiled from source on Linux.

Thanks,
Josh


CREATE TEMPORARY TABLE test_trg (
        colA  int,
        colB  text,
        colC  text,
        updated timestamp with time zone NOT NULL
) ON COMMIT DROP;

CREATE OR REPLACE FUNCTION pg_temp.track_updates()
RETURNS TRIGGER AS $$
DECLARE
BEGIN
    IF TG_OP = 'UPDATE' THEN
        IF NEW::text IS NOT DISTINCT FROM OLD::text THEN
            RAISE NOTICE 'OLD and NEW are the same!';
        ELSE
            RAISE NOTICE 'OLD and NEW are different!';
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER "track_updates_trg"
BEFORE INSERT OR UPDATE ON "pg_temp"."test_trg"
FOR EACH ROW EXECUTE PROCEDURE pg_temp.track_updates();

INSERT INTO pg_temp.test_trg (colA, colB, colC, updated)
VALUES (1, '', NULL, CURRENT_TIMESTAMP);

-- Each of these UPDATEs say 'OLD and NEW are different!'
UPDATE test_trg SET colB = NULL;
UPDATE test_trg SET colC = '';
UPDATE test_trg SET colC = NULL;
UPDATE test_trg SET colB = '', colC = '';

--
Footnotes:
[1] http://archives.postgresql.org/pgsql-hackers/2009-07/msg00040.php