Stored Procedure and Trigger they puzzle me - Mailing list pgsql-general

-----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@heidieker.de
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-----