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
--
С уважением,
Александр Пыхалов,
системный администратор ЮГИНФО ЮФУ.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
