problems with delete cascade

hello,

i'm having a problem with my db using the on delete cascade command.
maybe someone can help me out. i have simplified the problem on a
different, less complicated db. these are the tables:

--------------------------------------------------------------
create table goodbye (

goodbye_id serial primary key,
whenn varchar ,
whewre varchar ,
howdy integer ,
type text,
cannot varchar,
willdo varchar,
whatever varchar,
isbn int
);

create table hello (
hello_id serial primary key,
goodbye_id integer ,
whatever varchar ,
isbn integer,
constraint hello_goodbye_id_fk foreign key(goodbye_id) references goodbye(goodbye_id) on update set null on delete cascade);

"

create table adios (
adios_id serial primary key,
cannot varchar ,
willdo varchar
);

create table goodbye_adios (
goodbye_id integer,
adios_id integer,
constraint goodbye_adios_goodbye_id_fk foreign key(goodbye_id) references goodbye(goodbye_id) on update set null on delete cascade,
constraint goodbye_adios_adios_id_fk foreign key(adios_id) references adios(adios_id) on update set null on delete cascade);
---------------------------------------------------------------------------------------
There is also a trigger that goes along with this scenerio to populate the goodbye_adios table automatically, which is:

--------------------------------------------------
create function sick() returns opaque as '

declare

h hello%ROWTYPE;
g goodbye%ROWTYPE;
a adios%ROWTYPE;
ga goodbye_adios%ROWTYPE;
wn varchar;
we varchar;
wh varchar;
i int;
ho text;
c varchar;
w varchar;
x int;
t text;
y int;

begin
t := new.type;
w := new.willdo;
c := new.cannot;
i := new.isbn;
wh := new.whatever;
we := new.whewre;
wn := new.whenn;
ho := new.howdy;

if t = ''hello'' then
select currval (''goodbye_goodbye_id_seq'') into x;
insert into hello(goodbye_id, whatever, isbn) values (x, wh, i);
elsif t = ''adios'' then
insert into adios (cannot, willdo) values (c, w);
select currval (''goodbye_goodbye_id_seq'')into y;
select currval (''adios_adios_id_seq'')into x;
raise notice ''last value goodbye = %'', y;
raise notice ''last value adios = % '',x;
insert into goodbye_adios (goodbye_id, adios_id ) values (y,x);

elsif t != ''adios''or t != ''hello'' then
insert into goodbye (whenn, whewre, howdy, whatever, isbn, cannot, willdo) values (wn, we, ho, wh, i, c, w);

end if;

return null;

end;

' language 'plpgsql';

create trigger poop after insert or update on goodbye
for each row execute procedure sick();
--------------------------------------------------------
all fine so far. now when i try to delete from goodbye_adios, it will cascade and delete from the goodbye_adios table, from the goodbye table, but not from the adios table. i have tried to make a function that would overcome this problem, but failed at that too. i'll list it so maybe if someone can tweak it a little i can use it.
------------------------------------------------------------------------
create function pos() returns opaque as '

declare

g goodbye%ROWTYPE;
a adios%ROWTYPE;
ga goodbye_adios%ROWTYPE;
x int;
y int;
go int;
ad int;

begin

go := old.goodbye_id;
ad := old.adios_id;

select adios_id from goodbye_adios where adios_id = ad into x;
perform adios_id from adios where adios_id = x;
delete from adios where adios_id = x;

return null;
end;

'language 'plpgsql';

create trigger sop before delete on goodbye_adios
for each row execute procedure pos();
-----------------------------------------------------------------

what this function ends up doing is deleting from the goodbye table, the adios table, but not the goodbye_adios table. i cannot figure out how to delete from all three tables with one query or transaction. if anybody can help, i would greatly appreicate it.

sorry about the length but thanks in advance,

mike z

--
Mike Zouroudis
Intern
__________________________________________________
I.D.E.A.L. Technology Corporation - Orlando Office
http://www.idealcorp.com - 407.999.9870 x14