Trigger problem

From: Sarah Officer <officers(at)aries(dot)tucson(dot)saic(dot)com>
To: pgsql-general(at)postgreSQL(dot)org
Cc: officers(at)aries(dot)tucson(dot)saic(dot)com
Subject: Trigger problem
Date: 2000-01-19 18:27:53
Message-ID: 38860229.27F4DCF4@aries.tucson.saic.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)aries(dot)tucson(dot)saic(dot)com

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ed Loehr 2000-01-19 19:15:15 Re: [GENERAL] Trigger problem
Previous Message Sarah Officer 2000-01-19 18:05:44 Re: [GENERAL] psql problem describing tables