Thread: 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.

Something similar happened to me. Did you create the function, then the
triger, found some error in the function then drop the function and
created again? The table of triggers is pointing to a function's oid that
is different now (that's what I figured out because I don't have the
source code). Drop the function and the trigger and create them again.

Regards, Jesus.

On Mon, 17 Jul 2000, jprem wrote:

> 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.
>

> 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.