Comment 5 for bug 798255

I also found their is something wrong with my view extend_reporter.all_circulation. I may have carried over a bug that was identified already (but can't find the reference). I have also since added code to archive the copy stat cat. This is also important for our member libraries who will assign a temporary stat cat for course reserves. The code is updated as follows and I have removed the "all_circulation" view until I can fix the code.

BEGIN;

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

CREATE TABLE extend_reporter.aged_patron_stat_cats (
    id bigserial NOT NULL,
    aged_circ_id bigint NOT NULL,
    stat_cat text NOT NULL,
    stat_cat_entry text NOT NULL
);

ALTER TABLE ONLY extend_reporter.aged_patron_stat_cats
    ADD CONSTRAINT aged_patron_stat_cats_pkey PRIMARY KEY (id);

CREATE TABLE extend_reporter.aged_copy_stat_cats (
    id bigserial NOT NULL,
    aged_circ_id bigint NOT NULL,
    stat_cat text NOT NULL,
    stat_cat_entry text NOT NULL
);

ALTER TABLE ONLY extend_reporter.aged_copy_stat_cats
    ADD CONSTRAINT aged_copy_stat_cats_pkey PRIMARY KEY (id);

CREATE OR REPLACE VIEW extend_reporter.circulation_patron_stat_cats AS
  SELECT c.id, s.name, m.stat_cat_entry
  FROM action.circulation c JOIN (actor.stat_cat_entry_usr_map m JOIN actor.stat_cat s ON m.stat_cat = s.id) ON c.usr = m.target_usr
  UNION ALL
  SELECT aged_circ_id, stat_cat, stat_cat_entry FROM extend_reporter.aged_patron_stat_cats;

CREATE OR REPLACE VIEW extend_reporter.circulation_copy_stat_cats AS
  SELECT c.id, s.name as stat_cat, e.value as stat_cat_entry
  FROM action.circulation c JOIN (asset.stat_cat_entry_copy_map m JOIN (asset.stat_cat s JOIN asset.stat_cat_entry e ON s.id=e.stat_cat) ON m.stat_cat = s.id ) ON c.target_copy = m.owning_copy
  UNION ALL
  SELECT aged_circ_id, stat_cat, stat_cat_entry FROM extend_reporter.aged_copy_stat_cats;

CREATE OR REPLACE FUNCTION extend_reporter.age_circ_stat_cat_on_delete () RETURNS TRIGGER AS $$
DECLARE
found char := 'N';
BEGIN
    -- If there are any renewals for this circulation, don't archive or delete
    -- it yet. We'll do so later, when we archive and delete the renewals.
    SELECT 'Y' INTO found
    FROM action.circulation
    WHERE parent_circ = OLD.id
    LIMIT 1;

    IF found = 'Y' THEN
        RETURN NULL; -- don't delete
 END IF;

    -- Archive a copy of the old row to extend_reporter.aged_stat_cats
    INSERT INTO extend_reporter.aged_copy_stat_cats
  (aged_circ_id, stat_cat, stat_cat_entry)
      SELECT id, stat_cat, stat_cat_entry
  FROM extend_reporter.circulation_copy_stat_cats WHERE id = OLD.id;

    INSERT INTO extend_reporter.aged_patron_stat_cats
  (aged_circ_id, stat_cat, stat_cat_entry)
      SELECT id, name, stat_cat_entry
  FROM extend_reporter.circulation_patron_stat_cats WHERE id = OLD.id;

    RETURN OLD;

END;

$$ LANGUAGE 'plpgsql';

CREATE TRIGGER action_circulation_aging_stat_cat_tgr
 BEFORE DELETE ON action.circulation
 FOR EACH ROW
 EXECUTE PROCEDURE extend_reporter.age_circ_stat_cat_on_delete ();

END