Re: unexpected effect of FOREIGN KEY ON CASCADE DELETE - Mailing list pgsql-general

I'll fix it this way:


CREATE TABLE foob(id serial primary key, name varchar default '');
CREATE TABLE fooA(id serial primary key, fooBook int not null
references fooB(id) on update cascade on delete cascade DEFERRABLE,
name varchar default '');

CREATE FUNCTION foobarrB() RETURNS trigger AS
$_$
BEGIN
  BEGIN
    INSERT INTO foob_temp(id, name) VALUES(OLD.id, OLD.name);
  EXCEPTION
    WHEN undefined_table THEN
      CREATE TEMP TABLE foob_temp(id bigint not null, name varchar not
null) ON COMMIT DROP;
      INSERT INTO foob_temp(id, name) VALUES(OLD.id, OLD.name);
  END;

  RETURN OLD;
END;
$_$ LANGUAGE 'plpgsql';

CREATE TRIGGER foobbrrrred BEFORE DELETE ON fooB FOR EACH ROW EXECUTE
PROCEDURE foobarrB();

CREATE FUNCTION foobarrA() RETURNS trigger AS
$_$
DECLARE
  _name varchar;
BEGIN
  BEGIN
    SELECT name INTO _name FROM foob_temp WHERE id = OLD.fooBook;
  EXCEPTION
    WHEN undefined_table THEN
      SELECT name INTO _name FROM fooB WHERE id = OLD.fooBook;
  END;

  RAISE NOTICE 'foobarred %', _name;
  RETURN OLD;
END;
$_$ LANGUAGE 'plpgsql';

CREATE TRIGGER fooaarrrred BEFORE DELETE ON fooA FOR EACH ROW EXECUTE
PROCEDURE foobarrA();

insert into foob(name) select random()::varchar FROM generate_series(1,1000);
insert into fooa(name, fooBook) select random()::varchar, bb.id FROM
(select id from foob order by random() limit 1) bb,
generate_series(1,100);


DELETE FROM foob where id in (select foobook from fooa order by
random() limit 3);