create function and trigger pgsql

[wanaka]

Hi I have two tables.

CREATE TABLE "contact_person" (
"cpid" int4 DEFAULT nextval('public.contact_person_cpid_seq') NOT NULL ,
"creationdate" timestamp without time zone,
"modifydate" timestamp without time zone,
"firstname" varchar(50),
"lastname" varchar(50),
"homeph" varchar(15),
"workph" varchar(15),
"mobileph" varchar(15),
"otherph" varchar(15),
"email1" varchar(50),
"email2" varchar(50),
"comments" text,
"fax" varchar(15),
"title" varchar(50),
"ic" varchar(15),
"workext" varchar(5),
"telnet" varchar(15),
CONSTRAINT "contact_person_pkey" PRIMARY KEY ("cpid")
);

CREATE TABLE "contact_list" (
"cl_id" int4 DEFAULT nextval('public.contact_list_cl_id_seq') NOT NULL ,
"pid" int4 NOT NULL ,
"cpid" int4 NOT NULL ,
"priority" int2,
"group_desc" varchar(30),
CONSTRAINT "$1" FOREIGN KEY ("cpid") REFERENCES contact_person(cpid) MATCH FULL ,
CONSTRAINT "$2" FOREIGN KEY ("pid") REFERENCES project(pid) MATCH FULL ,
CONSTRAINT "contact_list_pkey" PRIMARY KEY ("cl_id")
);

If a contact_person record is removed, all related record in the contact_list table should be removed as well.

Is there any example on my scenario? I am using postgres 7.3

[wanaka]

Based on some example I have written this is this correct

create function del_contact_list () RETURNS opaque AS '
DECLARE
cpid INTERGER;
BEGIN
delete from contact_list where cpid=OLD.cpid;
END;'
LANGUATE 'plsql';

create trigger del_contact_list AFTER DELETE on contact_person
FOR EACH ROW EXECUTE PROCEDURE del_contact_list();

[Sxooter]

You need to just add " on delete cascade" to your table definitions.