Thread: trigger function in plpgsql (newbie)

Hello. I'm  a total newbie when it comes to plpgsql and trigger functions, but
here is my code that does not work

create table T1 (
    ID    integer primary key,
    v1    real );

create table T2 (
    RID    integer references  T1,
    when    date,
    v2    real,
    v1timesv2    real );

I whish to do the following:
Whenever I insert something into T2, I insert when and v2, I also wish to
calculate the value v1timesv2 and store it in the table. (the value of v1
changes over time). I wish to do this as a trigger function. I really don't
know how to do this with a plpgsql function, here is a futile attempt.

create function func_cal() RETURNS OPAQUE AS '
   BEGIN
      NEW.v1timesv2 := NEW.v2 * (select T1.v1 from T1 WHERE NEW.RID=T1.ID);
      RETURN NEW;
   END; '
LANGUAGE 'plpgsql';

create trigger trigger_happy AFTER INSERT ON  T2
    for each ROW EXECUTE PROCEDURE func_cal();

I guess you laugh at this, but I have not found any really good documentation
about this (or am I just stupid?) so please tell me what I do wrong.

By the way, in the name you write in the create trigger statement, when I
look at the man page for create_trigger it says
"name   The name of an existing trigger."
Eh... I thought I was creating a NEW trigger... any comments on this?

TIA,
Gunnar.

Gunnar Lindholm <gunnar@gunix.mine.nu> writes:
> Hello. I'm  a total newbie when it comes to plpgsql and trigger functions, but
> here is my code that does not work

You need a BEFORE INSERT trigger, not AFTER INSERT.  After the insertion
is too late to change the row that will be stored.

            regards, tom lane

On Sun, 28 Oct 2001, Gunnar Lindholm wrote:

> create trigger trigger_happy AFTER INSERT ON  T2
>     for each ROW EXECUTE PROCEDURE func_cal();

I think the only problem is that you want a BEFORE INSERT trigger, not
an AFTER INSERT trigger (before triggers can modify the row that is
inserted directly, since the row hasn't been inserted yet).  You may
also want to consider doing this for UPDATE as well since otherwise
the v1*v2 value won't be updated unless you do it manually.

Some general advice: often you will need both a BEFORE and an AFTER trigger
(split your logic up between the two) in order to get triggers right. If your
rows need a BEFORE trigger (they are constrained), then they often will need
an AFTER trigger (they are bound) too.  The BEFORE trigger is to do mainly
just the constraint decision about if the change should be allowed or not,
and the AFTER trigger takes bound action as a result of when a change
actually occurs. The RAISE EXCEPTION plpgsql statement will be especially
useful in the BEFORE trigger when you need to veto (abort/rollback) the
proposed change. The AFTER trigger can also RAISE EXCEPTION to undo
everything if needed.

Robert

On Sunday 28 October 2001 08:52 am, Gunnar Lindholm wrote:
> Hello. I'm  a total newbie when it comes to plpgsql and trigger functions,
> but here is my code that does not work
>
> create table T1 (
>     ID    integer primary key,
>     v1    real );
>
> create table T2 (
>     RID    integer references  T1,
>     when    date,
>     v2    real,
>     v1timesv2    real );
>
> I whish to do the following:
> Whenever I insert something into T2, I insert when and v2, I also wish to
> calculate the value v1timesv2 and store it in the table. (the value of v1
> changes over time). I wish to do this as a trigger function. I really don't
> know how to do this with a plpgsql function, here is a futile attempt.
>
> create function func_cal() RETURNS OPAQUE AS '
>    BEGIN
>       NEW.v1timesv2 := NEW.v2 * (select T1.v1 from T1 WHERE NEW.RID=T1.ID);
>       RETURN NEW;
>    END; '
> LANGUAGE 'plpgsql';
>
> create trigger trigger_happy AFTER INSERT ON  T2
>     for each ROW EXECUTE PROCEDURE func_cal();
>
> I guess you laugh at this, but I have not found any really good
> documentation about this (or am I just stupid?) so please tell me what I do
> wrong.
>
> By the way, in the name you write in the create trigger statement, when I
> look at the man page for create_trigger it says
> "name   The name of an existing trigger."
> Eh... I thought I was creating a NEW trigger... any comments on this?
>
> TIA,
> Gunnar.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org