Re: Stored Procedure and Trigger they puzzle me

Lars Heidieker wrote:
> 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,

I think you can do without this column; it's already defined by your
location path and it constrains your hierarchy to single parent nodes.

If you're sure single parent nodes are sufficient, you're probably
better off using the ltree contrib package. You'll still have to handle
tree integrity yourself, but you'll have an optimized index and
functions to navigate the tree.

>   type int2 NOT NULL default '0',

Why the typecast? A string isn't an integer, you know...

>   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',

And you're "forcing" your column names to lower case here; whether you
need to is up to you, of course.

>   PRIMARY KEY  (ltlocation_id,ltlocancester_id)
> )  ;


> 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;

Are you sure you want locations to reference themselves? That may also
be where your unique constraint violation originates.

I think I'd use something along the lines of:

workid := new.parent;
LOOP
    INSERT INTO ltlocationpath (ltlocation_id, ltlocancester_id)
    VALUES (new.id, workid);

    SELECT INTO workid ...

    -- Assuming the top nodes have NULL parents
    EXIT WHEN parent IS NULL;
END LOOP;

>     WHILE workid > 0 LOOP
>         BEGIN
>             EXECUTE 'INSERT INTO ltlocationpath (ltlocation_id,
> ltlocancester_id) '
>                 || 'VALUES (' || new.id || ', ' || workid || ')';

I don't think you need a dynamic query here (see my example).

>         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)

I think that is because your workid will be back at 25 in the next
iteration when that happens, because of the self-reference.

>
> It works OK with ignoring the exception but why is the exception thrown
> in the first place.
>
> The postgresql version 8.1.5

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend


--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //