Weeding Postgres Function

DROP FUNCTION rogan.weeding(INT);


CREATE OR REPLACE FUNCTION rogan.weeding(shelf_id INT)
  RETURNS TABLE (
        "count"                                 INT8,
        barcode                                 TEXT,
        "shelving location"                     TEXT,
        "call number"                           TEXT,
        title                                   TEXT,
        "last circulation"                      DATE
        ) AS
$$
BEGIN

DECLARE     weeding_age     INTERVAL,
            dispersal_max   INTEGER,
            weed_org        INTEGER



SELECT INTO weed_org acl.owning_lib FROM asset.copy_location acl WHERE acl.id = shelf_id;


SELECT INTO weeding_age rous.value
FROM rogan.org_unit_setting rous
JOIN actor.org_unit_ancestors_distance(weed_org) d ON (d.id = rous.org_unit)
WHERE NAME = 'weeding_age'
ORDER BY d.distance
LIMIT 1
;

SELECT INTO dispersal_max rous.value
FROM rogan.org_unit_setting rous
JOIN actor.org_unit_ancestors_distance(weed_org) d ON (d.id = rous.org_unit)
WHERE NAME = 'dispersal_max'
ORDER BY d.distance
LIMIT 1
;


create temp table dispersal_counts on commit drop as
select count(rdc.id) as d_count, rdc.copy_id
from rogan.dispersed_copies rdc
group by 2
;


CREATE TEMP TABLE weeds ON COMMIT DROP AS
SELECT COUNT(acirc.id) as circ_count, ac.id as copy_id, ac.barcode AS barcode, acl.NAME AS shelving_location,
acn.label AS call_number, msr.title, date(MAX(acirc.xact_start)) AS last_circulation
FROM asset.COPY ac
JOIN asset.copy_location acl ON ac.LOCATION = acl.id
LEFT JOIN ACTION.circulation acirc ON acirc.target_copy = ac.id
JOIN asset.call_number acn ON acn.id = ac.call_number
JOIN reporter.materialized_simple_record msr ON msr.id = acn.record
JOIN actor.org_unit child ON child.id = ac.circ_lib
WHERE acl.id = shelf_id
AND acirc.xact_start IS NULL
AND date(ac.create_date) <= NOW() - weeding_age
AND ac.deleted = FALSE
AND ac.status = 0
GROUP BY 2, 3, 4, 5, 6
UNION ALL
SELECT COUNT(acirc.id) as circ_count, ac.id as copy_id, ac.barcode AS barcode, acl.NAME AS shelving_location,
acn.label AS call_number, msr.title, date(MAX(acirc.xact_start)) AS last_circulation
FROM asset.COPY ac
JOIN asset.copy_location acl ON ac.LOCATION = acl.id
LEFT JOIN ACTION.circulation acirc ON acirc.target_copy = ac.id
JOIN asset.call_number acn ON acn.id = ac.call_number
JOIN reporter.materialized_simple_record msr ON msr.id = acn.record
JOIN actor.org_unit child ON child.id = ac.circ_lib
WHERE acl.id = shelf_id
AND date(ac.create_date) <= NOW() - weeding_age
AND ac.deleted = FALSE
AND ac.status = 0
GROUP BY 2, 3, 4, 5, 6
HAVING MAX(acirc.xact_start) < NOW() - weeding_age
ORDER BY 4
;

delete from weeds where copy_id in
    ( select w.copy_id
    from weeds w
    join dispersal_counts dc on dc.copy_id = w.copy_id
    where dc.d_count >= dispersal_max);


RETURN QUERY
SELECT circ_count, barcode, shelving_location, call_number, title, last_circulation
FROM weeds;


END;
$$
LANGUAGE plpgsql;

-- adult fiction
SELECT * FROM extend_reporter.weeding(1002);
-- ready reference, much better on test server
SELECT * FROM extend_reporter.weeding(1036);