Thread: 8.3 PL/pgSQL comparing arbitrary records
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
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 >
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
>
--
С уважением,
Александр Пыхалов,
системный администратор ЮГИНФО ЮФУ.
On Tue, Dec 15, 2009 at 1:23 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > 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 > Thanks, Pavel! This is exactly what I was looking for. I am able to use IF ROW(NEW.*) IS NOT DISTINCT FROM ROW(OLD.*) THEN ... on 8.3. Josh