From a0ecaaac4370defe0be8f0291e557ea4919588d7 Mon Sep 17 00:00:00 2001 From: Liam Whalen Date: Wed, 12 Aug 2015 15:15:42 -0700 Subject: [PATCH] LP1353643: moved SQL functions to single file. Added comments. This commit moves all SQL functions (new and modified) for the luri as copy improvements to a single upgrade file instead of placing them in the same file. Signed-off-by: Liam Whalen --- .../upgrade/XXXX.schema.functions.luri_as_copy.sql | 559 +++++++++++++++++++++ 1 file changed, 559 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.functions.luri_as_copy.sql diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.functions.luri_as_copy.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.functions.luri_as_copy.sql new file mode 100644 index 0000000000..229b244c9b --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.functions.luri_as_copy.sql @@ -0,0 +1,559 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +-- This new version of actor.org_unit_descendants allows a depth to be specified for descendants as well as ancestors. +-- This depth determines how far down into the descendant tree to go before returning results and how far up the ancestor +-- tree to go before returning results. +-- The first paramter specifies the org unit for which anscestors and descendants are wanted. +-- The second paramter specifies how far into the ancestor tree to ascend. +-- The third paramter specifies how far into the descendant tree to descend. +CREATE OR REPLACE FUNCTION actor.org_unit_descendants( INT, INT, INT ) RETURNS SETOF actor.org_unit AS $$ + WITH RECURSIVE 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) + WHERE ad.depth = $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 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 = $1 + 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) + ) SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id) WHERE depth <= $3; +$$ LANGUAGE SQL ROWS 1; + +-- This function returns a partial org unit tree. It limits the depth of the tree according to the second parameter. +-- This function returns all descendants, but limits the number of ancestors in the tree. +-- The first parameter is the org unit whose tree you want returned. +-- The second parameter is the ancestor depth to limit by. +CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_fenced_path( INT, INT ) RETURNS SETOF actor.org_unit AS $$ + WITH RECURSIVE org_unit_ancestors_distance(id, distance) AS ( + SELECT $1, 0 + UNION + SELECT ou.parent_ou, ouad.distance+1 + FROM actor.org_unit ou JOIN org_unit_ancestors_distance ouad ON (ou.id = ouad.id) + WHERE ou.parent_ou IS NOT NULL + ) + SELECT ou.* FROM actor.org_unit ou JOIN org_unit_ancestors_distance ouad USING (id) ORDER BY ouad.distance DESC OFFSET $2; +$$ LANGUAGE SQL ROWS 1; + +--This function returns a tree with ancestors and descendants of the OU in question. +--However, it also allows that tree to limit the number of ancestors and decendants to return +--based on depth limits. +--The first function is the OU in question. +--The second function is the ancestor depth to limit by. +--The third function is the descendant depth to limit by. + +CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT, INT, INT ) RETURNS SETOF actor.org_unit AS $$ + SELECT * + FROM actor.org_unit_ancestor_fenced_path($1, $2) + UNION + SELECT * + FROM actor.org_unit_descendants($1, (SELECT depth FROM actor.org_unit AS aou INNER JOIN + actor.org_unit_type AS aout ON aou.ou_type = aout.id + WHERE aou.id = $1), $3); +$$ LANGUAGE SQL STABLE ROWS 1; + +--The changes to query_parser_fts modify how OUs are structured for +--the query parser's WHERE owning_lib = parameters. These changes +--allow the owning_lib to be a partial tree limited by depth in both +--ancestor and descendant directions for both the search library and +--the preferred library. +--Four new variables are defined. +--search_anc_rec_vis_depth holds the database value for the YAOUS opac.luri_ancestor_record_visibility_depth. +--search_des_rec_vis_depth holds the database value for the YAOUS opac.luri_descendants_record_visibility_depth. +--pref_anc_rec_vis_depth holds the database value for the YAOUS opac.luri_pref_ancestor_record_visibility_depth. +--pref_des_rec_vis_depth holds the database value for the YAOUS opac.luri_pref_descendants_record_visibility_depth. +--These variables limit the display of records in search results. If a library does not want a record to display +--if it is owned by an OU ancestor or descendant of certain depths, then these limits are specified in the new +--YAOUS values. + +CREATE OR REPLACE FUNCTION search.query_parser_fts ( + + param_search_ou INT, + param_depth INT, + param_query TEXT, + param_statuses INT[], + param_locations INT[], + param_offset INT, + param_check INT, + param_limit INT, + metarecord BOOL, + staff BOOL, + deleted_search BOOL, + param_pref_ou INT DEFAULT NULL +) RETURNS SETOF search.search_result AS $func$ +DECLARE + + current_res search.search_result%ROWTYPE; + search_org_list INT[]; + luri_org_list INT[]; + tmp_int_list INT[]; + + check_limit INT; + core_limit INT; + core_offset INT; + tmp_int INT; + + core_result RECORD; + core_cursor REFCURSOR; + core_rel_query TEXT; + + total_count INT := 0; + check_count INT := 0; + deleted_count INT := 0; + visible_count INT := 0; + excluded_count INT := 0; + + luri_as_copy BOOL; + search_anc_rec_vis_depth INT; + search_des_rec_vis_depth INT; + pref_anc_rec_vis_depth INT; + pref_des_rec_vis_depth INT; + +BEGIN + + check_limit := COALESCE( param_check, 1000 ); + core_limit := COALESCE( param_limit, 25000 ); + core_offset := COALESCE( param_offset, 0 ); + + SELECT COALESCE( enabled, FALSE ) INTO luri_as_copy FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy'; + + SELECT COALESCE( substring(value, '\d+'), '0' ) INTO search_anc_rec_vis_depth + FROM actor.org_unit_ancestor_setting('opac.luri_ancestor_record_visibility_depth', param_search_ou); + + SELECT COALESCE( substring(value, '\d+'), '100' ) INTO search_des_rec_vis_depth + FROM actor.org_unit_ancestor_setting('opac.luri_descendants_record_visibility_depth', param_search_ou); + + SELECT COALESCE( substring(value, '\d+'), '0') INTO pref_anc_rec_vis_depth + FROM actor.org_unit_ancestor_setting('opac.luri_pref_ancestor_record_visibility_depth', param_pref_ou); + + SELECT COALESCE( substring(value, '\d+'), '100' ) INTO pref_des_rec_vis_depth + FROM actor.org_unit_ancestor_setting('opac.luri_pref_descendants_record_visibility_depth', param_pref_ou); + + -- core_skip_chk := COALESCE( param_skip_chk, 1 ); + + IF param_search_ou > 0 THEN + IF param_depth IS NOT NULL THEN + SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth ); + ELSE + SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou ); + END IF; + + IF luri_as_copy THEN + SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_full_path( param_search_ou, + search_anc_rec_vis_depth, search_des_rec_vis_depth ); + ELSE + SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_ancestor_fenced_path( param_search_ou, + search_anc_rec_vis_depth ); + END IF; + + ELSIF param_search_ou < 0 THEN + SELECT ARRAY_AGG(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou; + + FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP + + IF luri_as_copy THEN + SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( tmp_int, + search_anc_rec_vis_depth, search_des_rec_vis_depth ); + ELSE + SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestor_fenced_path( tmp_int, + search_anc_rec_vis_depth ); + END IF; + + luri_org_list := luri_org_list || tmp_int_list; + END LOOP; + + SELECT ARRAY_AGG(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id); + + ELSIF param_search_ou = 0 THEN + -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure. + END IF; + + IF param_pref_ou IS NOT NULL THEN + IF luri_as_copy THEN + SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( param_pref_ou, + pref_anc_rec_vis_depth, pref_des_rec_vis_depth ); + ELSE + SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestor_fenced_path( param_pref_ou, + pref_anc_rec_vis_depth ); + END IF; + + luri_org_list := luri_org_list || tmp_int_list; + END IF; + + OPEN core_cursor FOR EXECUTE param_query; + + LOOP + + FETCH core_cursor INTO core_result; + EXIT WHEN NOT FOUND; + EXIT WHEN total_count >= core_limit; + + total_count := total_count + 1; + + CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset; + + check_count := check_count + 1; + + IF NOT deleted_search THEN + + PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) ); + IF NOT FOUND THEN + -- RAISE NOTICE ' % were all deleted ... ', core_result.records; + deleted_count := deleted_count + 1; + CONTINUE; + END IF; + + PERFORM 1 + FROM biblio.record_entry b + JOIN config.bib_source s ON (b.source = s.id) + WHERE s.transcendant + AND b.id IN ( SELECT * FROM unnest( core_result.records ) ); + + IF FOUND THEN + -- RAISE NOTICE ' % were all transcendant ... ', core_result.records; + visible_count := visible_count + 1; + + current_res.id = core_result.id; + current_res.rel = core_result.rel; + + tmp_int := 1; + IF metarecord THEN + SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id; + END IF; + + IF tmp_int = 1 THEN + current_res.record = core_result.records[1]; + ELSE + current_res.record = NULL; + END IF; + + RETURN NEXT current_res; + + CONTINUE; + END IF; + + PERFORM 1 + FROM asset.call_number cn + JOIN asset.uri_call_number_map map ON (map.call_number = cn.id) + JOIN asset.uri uri ON (map.uri = uri.id) + WHERE NOT cn.deleted + AND cn.label = '##URI##' + AND uri.active + AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) ) + LIMIT 1; + + IF FOUND THEN + -- RAISE NOTICE ' % have at least one URI ... ', core_result.records; + visible_count := visible_count + 1; + + current_res.id = core_result.id; + current_res.rel = core_result.rel; + + tmp_int := 1; + IF metarecord THEN + SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id; + END IF; + + IF tmp_int = 1 THEN + current_res.record = core_result.records[1]; + ELSE + current_res.record = NULL; + END IF; + + RETURN NEXT current_res; + + CONTINUE; + END IF; + + IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN + + PERFORM 1 + FROM asset.call_number cn + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE NOT cn.deleted + AND NOT cp.deleted + AND cp.status IN ( SELECT * FROM unnest( param_statuses ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + LIMIT 1; + + IF NOT FOUND THEN + PERFORM 1 + FROM biblio.peer_bib_copy_map pr + JOIN asset.copy cp ON (cp.id = pr.target_copy) + WHERE NOT cp.deleted + AND cp.status IN ( SELECT * FROM unnest( param_statuses ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + LIMIT 1; + + IF NOT FOUND THEN + -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records; + excluded_count := excluded_count + 1; + CONTINUE; + END IF; + END IF; + + END IF; + + IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN + + PERFORM 1 + FROM asset.call_number cn + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE NOT cn.deleted + AND NOT cp.deleted + AND cp.location IN ( SELECT * FROM unnest( param_locations ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + LIMIT 1; + + IF NOT FOUND THEN + PERFORM 1 + FROM biblio.peer_bib_copy_map pr + JOIN asset.copy cp ON (cp.id = pr.target_copy) + WHERE NOT cp.deleted + AND cp.location IN ( SELECT * FROM unnest( param_locations ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + LIMIT 1; + + IF NOT FOUND THEN + -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records; + excluded_count := excluded_count + 1; + CONTINUE; + END IF; + END IF; + + END IF; + + IF staff IS NULL OR NOT staff THEN + + PERFORM 1 + FROM asset.opac_visible_copies + WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND record IN ( SELECT * FROM unnest( core_result.records ) ) + LIMIT 1; + + IF NOT FOUND THEN + PERFORM 1 + FROM biblio.peer_bib_copy_map pr + JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy) + WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + LIMIT 1; + + IF NOT FOUND THEN + + -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records; + excluded_count := excluded_count + 1; + CONTINUE; + END IF; + END IF; + + ELSE + + PERFORM 1 + FROM asset.call_number cn + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE NOT cn.deleted + AND NOT cp.deleted + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + LIMIT 1; + + IF NOT FOUND THEN + + PERFORM 1 + FROM biblio.peer_bib_copy_map pr + JOIN asset.copy cp ON (cp.id = pr.target_copy) + WHERE NOT cp.deleted + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + LIMIT 1; + + IF NOT FOUND THEN + + PERFORM 1 + FROM asset.call_number cn + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND NOT cp.deleted + LIMIT 1; + + IF NOT FOUND THEN + -- Recheck Located URI visibility in the case of no "foreign" copies + PERFORM 1 + FROM asset.call_number cn + JOIN asset.uri_call_number_map map ON (map.call_number = cn.id) + JOIN asset.uri uri ON (map.uri = uri.id) + WHERE NOT cn.deleted + AND cn.label = '##URI##' + AND uri.active + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cn.owning_lib NOT IN ( SELECT * FROM unnest( luri_org_list ) ) + LIMIT 1; + + IF FOUND THEN + -- RAISE NOTICE ' % were excluded for foreign located URIs... ', core_result.records; + excluded_count := excluded_count + 1; + CONTINUE; + END IF; + ELSE + -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records; + excluded_count := excluded_count + 1; + CONTINUE; + END IF; + END IF; + + END IF; + + END IF; + + END IF; + + visible_count := visible_count + 1; + + current_res.id = core_result.id; + current_res.rel = core_result.rel; + + tmp_int := 1; + IF metarecord THEN + SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id; + END IF; + + IF tmp_int = 1 THEN + current_res.record = core_result.records[1]; + ELSE + current_res.record = NULL; + END IF; + + RETURN NEXT current_res; + + IF visible_count % 1000 = 0 THEN + -- RAISE NOTICE ' % visible so far ... ', visible_count; + END IF; + + END LOOP; + + current_res.id = NULL; + current_res.rel = NULL; + current_res.record = NULL; + current_res.total = total_count; + current_res.checked = check_count; + current_res.deleted = deleted_count; + current_res.visible = visible_count; + current_res.excluded = excluded_count; + + CLOSE core_cursor; + + RETURN NEXT current_res; + +END; +$func$ LANGUAGE PLPGSQL; + +--This modifies evergreen.located_uris to return URIs based on a modified OU tree. +--It will limit URIs returned based on two YAOUS settings. +--opac.luri_anc_vis_depth limits the display of URIs owned by anscestors up to the depth specified. +--opac.luri_des_vis_depth limits the display of URIs ownd by descendants up to the depth specified. + +CREATE OR REPLACE FUNCTION evergreen.located_uris ( + bibid BIGINT[], + ouid INT, + pref_lib INT DEFAULT NULL +) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank INT) AS $$ + WITH all_orgs AS (SELECT COALESCE( enabled, FALSE ) AS flag FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy') + SELECT DISTINCT ON (id) * FROM ( + SELECT acn.id, COALESCE(aou.name,aoud.name), acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou + FROM asset.call_number acn + INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number + INNER JOIN asset.uri auri ON auri.id = auricnm.uri + LEFT JOIN actor.org_unit_ancestors( COALESCE($3, $2) ) aou ON (acn.owning_lib = aou.id) + LEFT JOIN actor.org_unit_descendants( COALESCE($3, $2) ) aoud ON (acn.owning_lib = aoud.id), + all_orgs + WHERE acn.record = ANY ($1) + AND acn.deleted IS FALSE + AND auri.active IS TRUE + AND ((NOT all_orgs.flag AND aou.id IS NOT NULL AND aou.id IN + (SELECT id FROM actor.org_unit_ancestor_fenced_path($2, (SELECT substring(value, '\d+')::INT + FROM actor.org_unit_setting + WHERE name = 'opac.luri_anc_vis_depth' AND org_unit = $2))) OR aou.id IN + (SELECT id FROM actor.org_unit_ancestor_fenced_path(COALESCE($3, $2), (SELECT substring(value, '\d+')::INT + FROM actor.org_unit_setting + WHERE name = 'opac.luri_anc_vis_depth' AND org_unit = COALESCE($3, $2))))) + OR (all_orgs.flag AND COALESCE(aou.id,aoud.id) IS NOT NULL AND ( + COALESCE(aou.id, aoud.id) IN + (SELECT id FROM actor.org_unit_full_path($2, (SELECT substring(value, '\d+')::INT + FROM actor.org_unit_setting + WHERE name = 'opac.luri_anc_vis_depth' AND org_unit = $2), + (SELECT substring(value, '\d+')::INT + FROM actor.org_unit_setting + WHERE name = 'opac.luri_des_vis_depth' AND org_unit = $2))) OR + COALESCE(aou.id, aoud.id) IN + (SELECT id FROM actor.org_unit_full_path(COALESCE($3, $2), (SELECT substring(value, '\d+')::INT + FROM actor.org_unit_setting + WHERE name = 'opac.luri_anc_vis_depth' AND org_unit = COALESCE($3, $2)), + (SELECT substring(value, '\d+')::INT + FROM actor.org_unit_setting + WHERE name = 'opac.luri_des_vis_depth' AND org_unit = COALESCE($3, $2))))))) + UNION + SELECT acn.id, COALESCE(aou.name,aoud.name) AS name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou + FROM asset.call_number acn + INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number + INNER JOIN asset.uri auri ON auri.id = auricnm.uri + LEFT JOIN actor.org_unit_ancestors( $2 ) aou ON (acn.owning_lib = aou.id) + LEFT JOIN actor.org_unit_descendants( $2 ) aoud ON (acn.owning_lib = aoud.id), + all_orgs + WHERE acn.record = ANY ($1) + AND acn.deleted IS FALSE + AND auri.active IS TRUE + AND ((NOT all_orgs.flag AND aou.id IS NOT NULL and aou.id IN + (SELECT id FROM actor.org_unit_ancestor_fenced_path($2, (SELECT substring(value, '\d+')::INT + FROM actor.org_unit_setting + WHERE name = 'opac.luri_anc_vis_depth' AND org_unit = $2))) OR aou.id IN + (SELECT id FROM actor.org_unit_ancestor_fenced_path(COALESCE($3, $2), (SELECT substring(value, '\d+')::INT + FROM actor.org_unit_setting + WHERE name = 'opac.luri_anc_vis_depth' AND org_unit = COALESCE($3, $2))))) + OR (all_orgs.flag AND COALESCE(aou.id,aoud.id) IS NOT NULL AND ( + COALESCE(aou.id, aoud.id) IN + (SELECT id FROM actor.org_unit_full_path($2, (SELECT substring(value, '\d+')::INT + FROM actor.org_unit_setting + WHERE name = 'opac.luri_anc_vis_depth' AND org_unit = $2), + (SELECT substring(value, '\d+')::INT + FROM actor.org_unit_setting + WHERE name = 'opac.luri_des_vis_depth' AND org_unit = $2))) OR + COALESCE(aou.id, aoud.id) IN + (SELECT id FROM actor.org_unit_full_path(COALESCE($3, $2), (SELECT substring(value, '\d+')::INT + FROM actor.org_unit_setting + WHERE name = 'opac.luri_anc_vis_depth' AND org_unit = COALESCE($3, $2)), + (SELECT substring(value, '\d+')::INT + FROM actor.org_unit_setting + WHERE name = 'opac.luri_des_vis_depth' AND org_unit = COALESCE($3, $2))))))))x + ORDER BY id, pref_ou DESC; +$$ +LANGUAGE SQL STABLE; + +COMMIT; -- 2.11.0