From 8a1bd596dff08951e274da83276e9a9f2506b6bb Mon Sep 17 00:00:00 2001 From: Rogan Hamby Date: Tue, 27 Feb 2018 15:59:12 -0500 Subject: [PATCH] find shelf function --- sql/base/base.sql | 23 +++++++++++++++++++++++ 1 files changed, 23 insertions(+), 0 deletions(-) diff --git a/sql/base/base.sql b/sql/base/base.sql index e734e45..4cc945d 100644 --- a/sql/base/base.sql +++ b/sql/base/base.sql @@ -3108,3 +3108,26 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TE END; $$ LANGUAGE PLPGSQL STRICT VOLATILE; +-- convenience functions for adding shelving locations +DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT); +CREATE OR REPLACE FUNCTION migration_tools.find_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$ +DECLARE + return_id INT; + d INT; + cur_id INT; +BEGIN + SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id); + WHILE d >= 0 + LOOP + SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d); + SELECT INTO return_id id FROM asset.copy_location WHERE owning_lib = cur_id AND name ILIKE shelf_name; + IF return_id IS NOT NULL THEN + RETURN return_id; + END IF; + d := d - 1; + END LOOP; + + RETURN NULL; +END +$$ LANGUAGE plpgsql; + -- 1.7.2.5