Stored Procedure and Trigger they puzzle me

[Lars Heidieker <lars@xxxxxxxxxxxx>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi all,

I just started to write my first stored procedure in plpgsql and  
installed a trigger for it.

The two Tables are:

CREATE TABLE ltlocation (
  "id" integer DEFAULT nextval('ltlocation_id_seq'::text) NOT NULL,
  name varchar(30) NOT NULL default '',
  "description" varchar(254) NOT NULL default '',
  "parent" int4,
  type int2 NOT NULL default '0',
  PRIMARY KEY  (id)
)  ;

just to hold a tree Structure and the second one is:

CREATE TABLE ltlocationpath (
  "ltlocation_id" int4 NOT NULL default '0',
  "ltlocancester_id" int4 NOT NULL default '0',
  PRIMARY KEY  (ltlocation_id,ltlocancester_id)
)  ;

where the second one holds a materialized path view of the first one.
These constraints are defined:

ALTER TABLE ONLY ltlocation ADD CONSTRAINT parent_fkey
		FOREIGN KEY (parent) REFERENCES ltlocation(id) ON UPDATE CASCADE ON  
DELETE CASCADE;
ALTER TABLE ONLY ltlocationpath ADD CONSTRAINT ltlocation_fkey
		FOREIGN KEY (ltlocation_id) REFERENCES ltlocation(id) ON UPDATE  
CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY ltlocationpath ADD CONSTRAINT ltlocancester_fkey
		FOREIGN KEY (ltlocancester_id) REFERENCES ltlocation(id) ON UPDATE  
CASCADE ON DELETE CASCADE;

The Stored Procedure is:

CREATE OR REPLACE FUNCTION populatelocationpath() RETURNS trigger AS $$
DECLARE
	workid integer := 0;
BEGIN	
	IF tg_op = 'UPDATE' THEN
		DELETE FROM ltlocationpath WHERE ltlocation_id = old.id;
	END IF;
	
	workid := new.id;
	WHILE workid > 0 LOOP
		BEGIN
			EXECUTE 'INSERT INTO ltlocationpath (ltlocation_id,  
ltlocancester_id) '
				|| 'VALUES (' || new.id || ', ' || workid || ')';
		EXCEPTION WHEN unique_violation THEN
			-- do nothing
		END;
				
		SELECT INTO workid parent FROM ltlocation WHERE id = workid;
	END LOOP;
	RETURN new;
END;
$$ LANGUAGE plpgsql;

And the Trigger is defined as:

CREATE TRIGGER ltlocationpathtrigger AFTER INSERT OR UPDATE ON  
ltlocation FOR EACH ROW EXECUTE PROCEDURE populatelocationpath();

The strange thing is:
insert is OK (materialized path gets populated)
update of parent column is OK old values get delete and new ones get  
inserted
but if the exception handling of the unique_violation exception is  
removed an update on the id column fails, with
an duplicate pkey violation an the self reference in the materialized  
path eg for the values (25, 25)

It works OK with ignoring the exception but why is the exception  
thrown in the first place.

The postgresql version 8.1.5

- --

Viele Grüße,
Lars Heidieker

lars@xxxxxxxxxxxx
http://paradoxon.info

- ------------------------------------

Mystische Erklärungen.
Die mystischen Erklärungen gelten für tief;
die Wahrheit ist, dass sie noch nicht einmal oberflächlich sind.
     -- Friedrich Nietzsche



-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (Darwin)

iD4DBQFFgbkKcxuYqjT7GRYRArhdAJ9s9uGGJX34mD2hGXgZxF78ZbBXIgCY6RvE
jhAObk1zUpvAZ4gGnFAk5w==
=qyV9
-----END PGP SIGNATURE-----