After create function and trigger, when I update "fams" by delete entry in
table "indi" I get this message :
ERROR: fmgr_info: function 19968: cache lookup failed
Here is an SQL script that makes this happen:
------------------------------------------------------------------------------
create table indi(
id serial primary key,
first_name varchar(40),
last_name varchar(40),
surname varchar(40),
sex char check (sex='M' or sex='F' or sex=''),
refn varchar(40) unique
check (first_name notnull or last_name notnull or surname notnull);
create table fams (
id serial primary key,
husb int check (sex(husb)='M' or sex(husb)=''),
wife int check (sex(wife)='F' or sex(wife)=''),
refn varchar(40) unique,
foreign key (husb) references indi on delete set null on update cascade,
foreign key (wife) references indi on delete set null on update cascade,
check (husb <> wife));
create function sex(int) returns char as 'select sex from indi where id = $1;'
language 'sql';
create function func_fams()
returns opaque
as '
delete from fams where (husb isnull and wife isnull);'
language 'plpgsql';
create trigger trig_fams after update on fams for each row execute procedure
func_fams();
pollet-lorand=> select * from indi;
id | first_name | last_name | surname | sex | refn
----+----------------------------+-----------+---------+-----+------
4 | Jacqueline Anna Marie-Ange | LORAND | | |
1 | Fabrice | POLLET | | M |
3 | Véronique | LEROY | | F |
9 | Maurice Joseph | POLLET | | |
(4 rows)
pollet-lorand=> select * from fams;
id | husb | wife | refn
----+------+------+------
1 | 1 | 3 |
4 | 9 | |
(2 rows)
pollet-lorand=> delete from indi where id=9;
ERROR: fmgr_info: function 19968: cache lookup failed
--
Fabrice POLLET
ENSTA / LEI / AMI
32 boulevard Victor Tél : +33 01 45 52 54 25
75739 PARIS CEDEX 15 Fax : +33 01 45 52 55 87
F R A N C E
