BEGIN; CREATE OR REPLACE FUNCTION evergreen.ranked_volumes( bibid BIGINT[], ouid INT, depth INT DEFAULT NULL, slimit HSTORE DEFAULT NULL, soffset HSTORE DEFAULT NULL, pref_lib INT DEFAULT NULL, includes TEXT[] DEFAULT NULL::TEXT[] ) RETURNS TABLE(id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$ WITH RECURSIVE ou_depth AS ( SELECT COALESCE( $3, ( SELECT depth FROM actor.org_unit_type aout INNER JOIN actor.org_unit ou ON ou_type = aout.id WHERE ou.id = $2 ) ) AS depth ), descendant_depth AS ( SELECT ou.id, ou.parent_ou, out.depth FROM actor.org_unit ou JOIN actor.org_unit_type out ON (out.id = ou.ou_type) JOIN anscestor_depth ad ON (ad.id = ou.id), ou_depth WHERE ad.depth = ou_depth.depth UNION ALL SELECT ou.id, ou.parent_ou, out.depth FROM actor.org_unit ou JOIN actor.org_unit_type out ON (out.id = ou.ou_type) JOIN descendant_depth ot ON (ot.id = ou.parent_ou) ), anscestor_depth AS ( SELECT ou.id, ou.parent_ou, out.depth FROM actor.org_unit ou JOIN actor.org_unit_type out ON (out.id = ou.ou_type) WHERE ou.id = $2 UNION ALL SELECT ou.id, ou.parent_ou, out.depth FROM actor.org_unit ou JOIN actor.org_unit_type out ON (out.id = ou.ou_type) JOIN anscestor_depth ot ON (ot.parent_ou = ou.id) ), descendants as ( SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id) ) SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM ( SELECT acn.id, aou.name, acn.label_sortkey, RANK() OVER w FROM asset.call_number acn JOIN asset.copy acp ON (acn.id = acp.call_number) JOIN descendants AS aou ON (acp.circ_lib = aou.id) WHERE acn.record = ANY ($1) AND acn.deleted IS FALSE AND acp.deleted IS FALSE AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN EXISTS ( SELECT 1 FROM asset.opac_visible_copies WHERE copy_id = acp.id AND record = acn.record ) ELSE TRUE END GROUP BY acn.id, acp.status, aou.name, acn.label_sortkey, aou.id WINDOW w AS ( ORDER BY COALESCE( CASE WHEN aou.id = $2 THEN -20000 END, CASE WHEN aou.id = $6 THEN -10000 END, (SELECT distance - 5000 FROM actor.org_unit_descendants_distance($6) as x WHERE x.id = aou.id AND $6 IN ( SELECT q.id FROM actor.org_unit_descendants($2) as q)), (SELECT e.distance FROM actor.org_unit_descendants_distance($2) as e WHERE e.id = aou.id), 1000 ), evergreen.rank_cp_status(acp.status) ) ) AS ua GROUP BY ua.id, ua.name, ua.label_sortkey ORDER BY rank, ua.name, ua.label_sortkey LIMIT ($4 -> 'acn')::INT OFFSET ($5 -> 'acn')::INT; $$ LANGUAGE SQL STABLE ROWS 10; CREATE OR REPLACE FUNCTION evergreen.ranked_volumes ( bibid BIGINT, ouid INT, depth INT DEFAULT NULL, slimit HSTORE DEFAULT NULL, soffset HSTORE DEFAULT NULL, pref_lib INT DEFAULT NULL, includes TEXT[] DEFAULT NULL::TEXT[] ) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$ SELECT * FROM evergreen.ranked_volumes(ARRAY[$1],$2,$3,$4,$5,$6,$7) $$ LANGUAGE SQL STABLE; COMMIT;