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

Hello

NEW and OLD is comparable in 8.4. In 8.3 and older you have to use
little bit different syntax

http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_compare_variables_NEW_and_OLD_in_trigger.27s_body

Regards
Pavel Stehule

2009/12/15 Josh Kupershmidt <schmiddy@gmail.com>:
> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>