-- Evergreen DB patch XXXX.lp790329_opac_lasso_counts.sql -- -- Resolves an error in calculating copy counts for org lassos -- Per LP 790329 -- BEGIN; -- check whether patch can be applied SELECT evergreen.upgrade_deps_block_check('0603', :eg_version); -- FIXME: add/check SQL statements to perform the upgrade CREATE OR REPLACE FUNCTION asset.opac_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ DECLARE ans RECORD; trans INT; BEGIN SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP RETURN QUERY SELECT -1, ans.id, COUNT( av.id ), SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), COUNT( av.id ), trans FROM actor.org_unit_descendants(ans.id) d JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id) JOIN asset.copy cp ON (cp.id = av.copy_id) GROUP BY 1,2,6; IF NOT FOUND THEN RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; END IF; END LOOP; RETURN; END; $f$ LANGUAGE PLPGSQL; COMMIT;