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

Hello.
It seems that this works (I made tests on permanent table, postgresql
8.4.0):

CREATE OR REPLACE FUNCTION track_updates()
RETURNS TRIGGER AS $$
DECLARE
BEGIN
     IF TG_OP = 'UPDATE' THEN
         IF NEW IS NOT DISTINCT FROM OLD 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;


 > update test_trg set cola=1 where cola=1;
NOTICE:  OLD and NEW are the same!

 > update test_trg set cola=2 where cola=1;
NOTICE:  OLD and NEW are different!

Josh Kupershmidt wrote:
> 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
>


--
С уважением,
Александр Пыхалов,
системный администратор ЮГИНФО ЮФУ.