3 INSERT INTO config.upgrade_log (version) VALUES ('0067');
5 CREATE TYPE action.hold_stats AS (
9 total_copy_ratio FLOAT,
10 available_copy_ratio FLOAT
13 CREATE OR REPLACE FUNCTION action.copy_related_hold_stats (copy_id INT) RETURNS action.hold_stats AS $func$
15 output action.hold_stats%ROWTYPE;
18 available_count INT := 0;
22 output.hold_count := 0;
23 output.copy_count := 0;
24 output.available_count := 0;
26 SELECT COUNT( DISTINCT m.hold ) INTO hold_count
27 FROM action.hold_copy_map m
28 JOIN action.hold_request h ON (m.hold = h.id)
29 WHERE m.target_copy = copy_id
32 output.hold_count := hold_count;
34 IF output.hold_count > 0 THEN
36 SELECT DISTINCT m.target_copy,
38 FROM action.hold_copy_map m
39 JOIN asset.copy acp ON (m.target_copy = acp.id)
40 JOIN action.hold_request h ON (m.hold = h.id)
41 WHERE m.hold IN ( SELECT DISTINCT hold FROM action.hold_copy_map WHERE target_copy = copy_id ) AND NOT h.frozen
43 output.copy_count := output.copy_count + 1;
44 IF hold_map_data.status IN (0,7,12) THEN
45 output.available_count := output.available_count + 1;
48 output.total_copy_ratio = output.copy_count::FLOAT / output.hold_count::FLOAT;
49 output.available_copy_ratio = output.available_count::FLOAT / output.hold_count::FLOAT;
56 $func$ LANGUAGE PLPGSQL;