Re: urgent help on function/trigger

> hello,
> i have a procedure and a trigger as below
> ______________________________________________________________________
> create function FUNCTEST () returns opaque
> as
> 'begin
> delete from X where COLX in (select COLX from X
> where COLX not in (select COLY from Y ));
> return null;
> end;'
> language 'plpgsql';
>
> create trigger TRIGTEST after delete on Y for each row
> execute procedure FUNCTEST();
> ________________________________________________________________________
>
> i need some tuples to be deleted from X while i delete some from Y.
>
> the function and trigger are created without any problem.
> when i delete a tuple from Y , i get the following error.
> _________________________________________________________________________
>
> ERROR: fmgr_info: function 152480: cache lookup failed
> _________________________________________________________________________
>
> why this error is due to ? can anyone help me on this ?
> thanx in advance.

Well, this error appears when you recreate the function without dropping the
trigger. You should drop and create the trigger every time you drop the
function.

BTW, you can implement the feature you want using FOREIGN KEY constraints -
they do the same things. Just create your tables as follows:

CREATE TABLE Y (
...,
COLY INT4,
...,
);
CREATE TABLE X (
...,
COLX INT4,
...,
CONSTRAINT fk_colx_coly
FOREIGN KEY (COLX)
REFERENCES Y(COLY)
ON DELETE CASCADE
);

and this feature will be implemented automatically.

Alex Bolenok.