problems with delete cascade - Mailing list pgsql-general

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
deletecascade, 
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,
fromthe goodbye table, but not from the adios table.  i have tried to make a function that would overcome this problem,
butfailed 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.
icannot figure out how to delete from all three tables with one query or transaction.  if anybody can help, i would
greatlyappreicate 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