trigger function in plpgsql (newbie)

From: Gunnar Lindholm <gunnar(at)gunix(dot)mine(dot)nu>
To: pgsql-general(at)postgresql(dot)org
Subject: trigger function in plpgsql (newbie)
Date: 2001-10-28 13:52:50
Message-ID: 01102814525001.00510@fire
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Marko Kreen 2001-10-28 15:09:45 Re: UNICODE
Previous Message bpalmer 2001-10-28 13:40:55 Re: Directory Names????