[BUGS] BUG #4380: Comparison of OLD and NEW columns in trigger does not always work

The following bug has been logged online:

Bug reference: 4380
Logged by: Daryl Joubert
Email address: daryl@ftcxpress.com
PostgreSQL version: 8.3.3
Operating system: WinXP SP2
Description: Comparison of OLD and NEW columns in trigger does not
always work
Details:

When comparing NEW.column_name to OLD.column_name in 'PG_UPDATE' operation
in a trigger, I get unpredictable results. Use the following script to
create the "Business Partner" table and its trigger, then add a few rows of
data to "First Name", "Middle Name" and "Last Name" using pgAdmin, and watch
how "Name" is built up for the former 3 columns.

Then: change the "Middle Name" column of an existing row repeatedly and you
should see that sometimes "Name" is updated, sometimes not. I can repeat the
problem here within 5 to 10 changes of "Middle Name". Am I doing something
wrong? Here is the script:

CREATE TABLE "Business Partner"
(
"Partner Number" serial NOT NULL,
"First Name" character varying,
"Middle Name" character varying,
"Last Name" character varying,
"Name" character varying,
CONSTRAINT "PK Business Partner" PRIMARY KEY ("Partner Number")
)
WITH (OIDS=FALSE);

CREATE FUNCTION t_business_partner()
RETURNS trigger AS
$BODY$
DECLARE
BEGIN
IF (TG_OP = 'INSERT') THEN
IF NEW."Name" IS NULL OR NEW."Name" = '' THEN
IF NEW."Middle Name" IS NULL OR NEW."Middle Name" = '' THEN
NEW."Name" := NEW."First Name" || ' ' || NEW."Last Name";
ELSE
NEW."Name" := NEW."First Name" || ' ' || NEW."Middle Name" || ' ' ||
NEW."Last Name";
END IF;
END IF;
NEW."Name" = initcap(NEW."Name");
NEW."First Name" = initcap(NEW."First Name");
NEW."Middle Name" = initcap(NEW."Middle Name");
NEW."Last Name" = initcap(NEW."Last Name");
RETURN NEW;
END IF;

IF (TG_OP = 'UPDATE') THEN
IF NEW."Name" IS NULL OR NEW."Name" = ''
OR NEW."First Name" <> OLD."First Name"
OR NEW."Middle Name" <> OLD."Middle Name"
OR NEW."Last Name" <> OLD."Last Name" THEN
IF NEW."Middle Name" IS NULL OR NEW."Middle Name" = '' THEN
NEW."Name" = NEW."First Name" || ' ' || NEW."Last Name";
ELSE
NEW."Name" = NEW."First Name" || ' ' || NEW."Middle Name" || ' ' ||
NEW."Last Name";
END IF;
NEW."Name" = initcap(NEW."Name");
NEW."First Name" = initcap(NEW."First Name");
NEW."Middle Name" = initcap(NEW."Middle Name");
NEW."Last Name" = initcap(NEW."Last Name");
END IF;
RETURN NEW;
END IF;

IF (TG_OP = 'DELETE') THEN
RETURN OLD;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;

CREATE TRIGGER "TR Business Partner"
BEFORE INSERT OR UPDATE OR DELETE
ON "Business Partner"
FOR EACH ROW
EXECUTE PROCEDURE t_business_partner();

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs