I am trying to create a simple trigger function. With some help
from the mailing list, I managed to create a trigger and functions.
Unfortunately I get an error message when I delete from the table
which has the trigger. Can anyone help me spot the error? Here's
what I have done:
CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
'/achilles_usr12/mars/swl/IRIX6.5/lib/plpgsql.so' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';
create table Images (
id varchar(100) PRIMARY KEY,
title varchar(25) NOT NULL,
filepath varchar(256) NOT NULL UNIQUE,
status_code varchar(5) NOT NULL
) ;
create table Istatus (
status_code varchar(5) PRIMARY KEY,
status_desc varchar(100) NOT NULL
);
CREATE FUNCTION remove_status_func()
RETURNS opaque AS '
delete from Images
where Images.status_code = old.status_code ;
select 1 as val;
' LANGUAGE 'plpgsql' ;
CREATE TRIGGER Istatus_delete_trigger
AFTER DELETE ON Istatus
FOR EACH ROW
EXECUTE PROCEDURE remove_status_func() ;
Insert into Istatus(status_code, status_desc)
values('A1', 'A1 Desc');
Insert into Istatus(status_code, status_desc)
values('A2', 'A2 Desc');
Insert into Istatus(status_code, status_desc)
values('A3', 'A3 Desc');
Insert into Images(id, title, filepath, status_code)
values ('ID1', 'First Image', '/usr/local/foo.gif', 'A1');
Insert into Images(id, title, filepath, status_code)
values ('ID2', 'Another Image', '/usr/local/bar.gif', 'A2');
> select * from istatus;
status_code|status_desc
-----------+-----------
A1 |A1 Desc
A2 |A2 Desc
A3 |A3 Desc
(3 rows)
> select * from images;
id |title |filepath |status_code
---+-------------+------------------+-----------
ID1|First Image |/usr/local/foo.gif|A1
ID2|Another Image|/usr/local/bar.gif|A2
(2 rows)
> delete from istatus where status_code = 'A1';
ERROR: fmgr_info: function 18848: cache lookup failed
What is the problem with the cache lookup? Any suggestions would be
appreciated.
Sarah Officer
officers@aries.tucson.saic.com
