Re: How to avoid the Mutating Triggers ?? - Mailing list pgsql-admin

From Stephan Szabo
Subject Re: How to avoid the Mutating Triggers ??
Date
Msg-id 20030219183512.P40199-100000@megazone23.bigpanda.com
Whole thread Raw
In response to Re: How to avoid the Mutating Triggers ??  ("Yudha Setiawan" <yudha@BonBon.net>)
List pgsql-admin
On Thu, 20 Feb 2003, Yudha Setiawan wrote:

> Dear Szabo, You were wrote;
>
> > Well, what have you tried?  For the simple
> > case (pointer is not changing), a not terribly
> > efficient version might be something like:
> >
> > create or replace function dorecalcupd() returns trigger as
> > 'declare
> >   last int;
> >  begin
> >  SELECT INTO last qty FROM foo where pointer < NEW."pointer" ORDER BY
> > pointer desc LIMIT 1;
> >  NEW.qty := last + NEW."in" - NEW."out";
> >  if (NEW.pointer = OLD.POINTER AND (NEW."in" != OLD."in" or NEW."out" !=
> > OLD."out")) then
> >   UPDATE foo set qty = (select sum(f."in") - sum(f."out") from foo f where
> > f.pointer<pointer)
> >    where pointer>NEW.pointer;
> >  end if;
> >  return NEW;
> >  end;' language 'plpgsql';
> >
> > In this case the trigger does cause requests to the same trigger for the
> > later rows, but since only qty is changing it does nothing.
> >
> > It's more complicated when you deal with the pointer moving case.
>
> Thank's a Lot for all milist especialy for dear Stephan Szabo. I'm Sorry
> "Pointer" on my ilustration  "IS NOT a FIELD". it's just information for
> me, to show you where is exactly the position of record / Pointer that
> I want to Change.
> Perhaps you have another best one, please...??

Well, what's the key that you're ordering by?  Use that instead of pointer
in the above.

If you have no ordering key or the key isn't guaranteed to be unique,
the original question doesn't have an answer because there is no implict
order to rows in a table.


pgsql-admin by date:

Previous
From: "Yudha Setiawan"
Date:
Subject: Re: How to avoid the Mutating Triggers ??
Next
From: Tom Lane
Date:
Subject: Re: dumpBlobs(): could not open large object: ERROR: inv_open: large object 23312462 not found