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@xxxxxxxxx>: > 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
