Re: 8.3 PL/pgSQL comparing arbitrary records
От | Alexander Pyhalov |
---|---|
Тема | Re: 8.3 PL/pgSQL comparing arbitrary records |
Дата | |
Msg-id | 4B2730CE.1080005@rsu.ru обсуждение исходный текст |
Ответ на | 8.3 PL/pgSQL comparing arbitrary records (Josh Kupershmidt <schmiddy@gmail.com>) |
Список | 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 > -- С уважением, Александр Пыхалов, системный администратор ЮГИНФО ЮФУ.
В списке pgsql-general по дате отправления: