Thread: Triggers

The technical reference gives an example of a trigger on a table - employee
Just to test this, I have created the following table,
CREATE TABLE employee
(name VARCHAR(30),
age int4,
state VARCHAR(2),
manager VARCHAR(3),
adult VARCHAR(3)); 

The I created a simple Function, as follows :

CREATE FUNCTION trig_insert_update_check_emp() RETURNS opaque AS '
BEGIN
IF new.age > 20 THEN
new.adult = ''yes'';
ELSE
new.adult = ''no'';
END IF;
END;
' LANGUAGE 'plpgsql';

Finally, I defined the Trigger as :

CREATE TRIGGER employee_insert_update
BEFORE INSERT OR UPDATE ON employee
FOR EACH ROW EXECUTE PROCEDURE trig_insert_update_check_emp();

Now, when I execute the following :

INSERT INTO employee (name,age,state,manager)
VALUES ('sean',29,'tx','yes');

I get :

ERROR fmgr_info function 6264440 cache lookup failed


What am I doing wrong ????

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thursday 26 February 2004 2:18 am, Philip J. Boonzaaier wrote:
> The technical reference gives an example of a trigger on a table -
> employee Just to test this, I have created the following table,
> CREATE TABLE employee
> (name VARCHAR(30),
> age int4,
> state VARCHAR(2),
> manager VARCHAR(3),
> adult VARCHAR(3));
>
> The I created a simple Function, as follows :
>
> CREATE FUNCTION trig_insert_update_check_emp() RETURNS opaque AS '
> BEGIN
> IF new.age > 20 THEN
> new.adult = ''yes'';
> ELSE
> new.adult = ''no'';
> END IF;
> END;
> ' LANGUAGE 'plpgsql';

Couple of comments:
1) Your trigger functions needs to return something. In this case, you
would "RETURN NEW".
2) I believe trigger functions need to "RETURNS TRIGGER", not "RETURNS
opaque", but I could be wrong. Give that a try.

Question:
1) What version of PostgreSQL are you running?

- --
Jonathan Gardner
jgardner@jonathangardner.net
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFASKulqp6r/MVGlwwRAgpLAKCycwL/i+/mb6bW1W4QjHKBO9e0xQCgl42q
pmohSw7PZiuIWgOQXxtgvI0=
=4iDJ
-----END PGP SIGNATURE-----

Philip J. Boonzaaier wrote:
> The technical reference gives an example of a trigger on a table - employee
> Just to test this, I have created the following table,
> CREATE TABLE employee
> (name VARCHAR(30),
> age int4,
> state VARCHAR(2),
> manager VARCHAR(3),
> adult VARCHAR(3)); 
> 
> The I created a simple Function, as follows :
> 
> CREATE FUNCTION trig_insert_update_check_emp() RETURNS opaque AS '

The "opaque" type has been split into several different ones, if you're 
using a recent version of PG, you'll want "RETURNS trigger".

[snip]
> Now, when I execute the following :
> 
> INSERT INTO employee (name,age,state,manager)
> VALUES ('sean',29,'tx','yes');
> 
> I get :
> 
> ERROR fmgr_info function 6264440 cache lookup failed

This usually means you redefined something - PG isn't smart enough (yet) 
to re-check all the dependencies in something like this. I'd guess you 
dropped/recreated the function after defining the trigger, yes?

The solution in older versions is to put the CREATE FUNCTION and CREATE 
TRIGGER in the same file so that you drop/recreate both together.

HTH
--   Richard Huxton  Archonet Ltd