Testing whether I can `CREATE OR REPLACE` the function used by a database trigger.

trigger_update_testscript.sql

begin;
  -- create two tables
  drop table if exists a;
  create table a (
    id bigserial NOT NULL primary key,
    value text
  );

  drop table if exists stats;
  create table stats (
    total int
  );
commit;

-- insert some values before we have trigger
insert into a (value) values ('foobar1');
insert into a (value) values ('foobar2');

begin;
  -- create trigger function
  CREATE OR REPLACE FUNCTION recalc_stats() RETURNS trigger
      LANGUAGE plpgsql
      AS $$ BEGIN
        INSERT INTO stats (total) SELECT COUNT(*) FROM newtab;
        RETURN NULL;
    END
  $$;

  DROP TRIGGER IF EXISTS trig_recalc_stats ON a;
  -- create trigger on `a` to call recalc stats
  CREATE TRIGGER trig_recalc_stats
  AFTER INSERT ON a
  REFERENCING NEW TABLE AS newtab
  FOR EACH STATEMENT EXECUTE FUNCTION recalc_stats();
commit;

  -- insert something in `a`
insert into a (value) values ('foobar3');
insert into a (value) values ('foobar4');

-- confirm that two rows were inserted into stats
select * from stats;

begin;
  -- replace the function called by trigger to always insert 99
  CREATE OR REPLACE FUNCTION recalc_stats() RETURNS trigger
      LANGUAGE plpgsql
      AS $$ BEGIN
        INSERT INTO stats (total) VALUES (99);
        RETURN NULL;
    END
  $$;
commit;

-- insert two rows
insert into a (value) values ('foobar5');
insert into a (value) values ('foobar6');

-- did the trigger insert 99 or total count?
select * from stats;


begin;
  -- replace the function called by trigger to always insert 99
  CREATE OR REPLACE FUNCTION recalc_stats() RETURNS trigger
      LANGUAGE plpgsql
      AS $$ BEGIN
        INSERT INTO stats (total) VALUES (200);
        RETURN NULL;
    END
  $$;
commit;

-- insert two rows
insert into a (value) values ('foobar7');
insert into a (value) values ('foobar8');

-- did the trigger insert 200 or total count?
select * from stats;