DROP FUNCTION IF EXISTS level1_table_ri_parent_del_func();
DROP FUNCTION IF EXISTS level1_table_ri_parent_upd_func();
DROP FUNCTION IF EXISTS level2_table_ri_child_insupd_func();

CREATE TABLE level1_table
(
      level1_no serial NOT NULL ,
      level1_node_name varchar(255),
       PRIMARY KEY (level1_no)
) WITHOUT OIDS;

CREATE TABLE level2_table
(
      level2_no serial NOT NULL ,
      parent_no int NOT NULL,
      level1_node_name varchar(255),
       PRIMARY KEY (level2_no)
) WITHOUT OIDS;

CREATE TABLE all_level_status
(
      level int NOT NULL,
      node_no int NOT NULL,
      status int,
       PRIMARY KEY (level, node_no)
) WITHOUT OIDS;

-- -- to test with existing trigger code, uncomment this and comment out
-- -- functions and triggers below.
-- ALTER TABLE level2_table
--        ADD FOREIGN KEY (parent_no)
--        REFERENCES level1_table (level1_no)
--        ON UPDATE RESTRICT
--        ON DELETE RESTRICT
-- ;

CREATE OR REPLACE FUNCTION level1_table_ri_parent_del_func()
  RETURNS TRIGGER
  LANGUAGE plpgsql
AS $$
  DECLARE n bigint;
  BEGIN
    PERFORM FROM p JOIN level2_table c ON c.parent_no = p.level1_no;
    IF FOUND THEN
      RAISE EXCEPTION 'RI error';
    END IF;
    RETURN NULL;
  END;
$$;

CREATE TRIGGER level1_table_ri_parent_del_trigger
  AFTER DELETE ON level1_table
  REFERENCING OLD TABLE AS p
  FOR EACH STATEMENT EXECUTE PROCEDURE level1_table_ri_parent_del_func();

CREATE OR REPLACE FUNCTION level1_table_ri_parent_upd_func()
  RETURNS TRIGGER
  LANGUAGE plpgsql
AS $$
  DECLARE
    x int;
  BEGIN
    WITH p AS (SELECT level1_no, sum(delta) cnt
                 FROM (SELECT level1_no, 1 AS delta FROM i
                       UNION ALL
                       SELECT level1_no, -1 AS delta FROM d) w
                 GROUP BY level1_no
                 HAVING sum(delta) < 0)
    SELECT level1_no FROM p JOIN level2_table c ON c.parent_no = p.level1_no
      INTO x;
    IF FOUND THEN
      RAISE EXCEPTION 'RI error';
    END IF;
    RETURN NULL;
  END;
$$;

CREATE TRIGGER level1_table_ri_parent_upd_trigger
  AFTER UPDATE ON level1_table
  REFERENCING OLD TABLE AS d NEW TABLE AS i
  FOR EACH STATEMENT EXECUTE PROCEDURE level1_table_ri_parent_upd_func();

CREATE OR REPLACE FUNCTION level2_table_ri_child_insupd_func()
  RETURNS TRIGGER
  LANGUAGE plpgsql
AS $$
  BEGIN
    PERFORM FROM c
      LEFT JOIN level1_table p
        ON p.level1_no IS NOT NULL AND p.level1_no = c.parent_no
      WHERE p.level1_no IS NULL;
    IF FOUND THEN
      RAISE EXCEPTION 'RI error';
    END IF;
    RETURN NULL;
  END;
$$;

CREATE TRIGGER level2_table_ri_child_insupd_trigger
  AFTER INSERT OR UPDATE ON level2_table
  REFERENCING NEW TABLE AS c
  FOR EACH STATEMENT EXECUTE PROCEDURE level2_table_ri_child_insupd_func();


--create test data
INSERT INTO level1_table(
            level1_no)
select generate_series(1,200);

INSERT INTO level2_table(
            level2_no, parent_no)
select
level2_no,level2_no/50+1 as parent_no
from
generate_series(1,9999) level2_no ;

INSERT INTO all_level_status(
            level, node_no, status)
select 1,level1_no,0
from level1_table;

INSERT INTO all_level_status(
            level, node_no, status)
select 2,level2_no,0
from level2_table;

INSERT INTO level1_table(
            level1_no)
select generate_series(201,1000000);


vacuum freeze analyze;
checkpoint;
vacuum analyze;
vacuum analyze;
checkpoint;


\timing on
DELETE FROM level1_table WHERE level1_no BETWEEN 201 AND 1000000;
