Skip site navigation (1) Skip section navigation (2)

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 (view raw, whole thread or download thread mbox)
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 '
      NEW.v1timesv2 := NEW.v2 * (select T1.v1 from T1 WHERE NEW.RID=T1.ID);
   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?



pgsql-general by date

Next:From: Marko KreenDate: 2001-10-28 15:09:45
Subject: Re: UNICODE
Previous:From: bpalmerDate: 2001-10-28 13:40:55
Subject: Re: Directory Names????

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group