Re: Problems understanding functions.

On Tue, 2002-09-17 at 16:47, Duncan Adams (DNS) wrote:
> Hi all
>
> I must be reading the wrong documentation but i just can't get the following
> function working.
> I don't get an error but it appears as if the function goes in to a loop.
> I could do this with PHP but would really like to get it right in plpgsql.
>
> could someone please look at it and also add a 'plpgsql for dummies' URL
>
> thanx Duncan.
>
> drop function fun_power_ups();
> create function fun_power_ups() RETURNS OPAQUE as '
>     begin
>         update power set ups = substring(power.phase from 1 for 1)
> where old.power_id = new.power_id;
>         return new;
>     end;
> ' language 'plpgsql';
>
> drop trigger tri_power_ups on power;
> create trigger tri_power_ups after insert or update on power for each row
> execute PROCEDURE fun_power_ups();
>
> (this is all on one table.)

So on UPDATE or INSERT on power, you update power... and so on
recursively until you exhaust all available memory!

Instead you want to modify NEW directly and return it.  Then make the
trigger a before trigger so that it will actually use the returned row.

CREATE OR REPLACE FUNCTION fun_power_ups() RETURNS opaque AS '
BEGIN
  NEW.ups = SUBSTRING(NEW.phase FROM 1 FOR 1);
  RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER tri_power_ups BEFORE INSERT OR UPDATE ON power FOR EACH
ROW EXECUTE PROCEDURE fun_power_ups();

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Honour the LORD with thy substance, and with the
      firstfruits of all thine increase; So shall thy barns
      be filled with plenty, and thy presses shall burst out
      with new wine."      Proverbs 3:9,10