From 412f16b3077c64d403fd78d45a1a7056235fcf7d Mon Sep 17 00:00:00 2001 From: miker Date: Wed, 25 Jan 2006 21:47:15 +0000 Subject: [PATCH] depth away full-path SP git-svn-id: svn://svn.open-ils.org/ILS/trunk@2841 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/020.schema.functions.sql | 13 +++++++++++++ 1 file changed, 13 insertions(+) diff --git a/Open-ILS/src/sql/Pg/020.schema.functions.sql b/Open-ILS/src/sql/Pg/020.schema.functions.sql index 04e361a3dc..88934fc397 100644 --- a/Open-ILS/src/sql/Pg/020.schema.functions.sql +++ b/Open-ILS/src/sql/Pg/020.schema.functions.sql @@ -102,6 +102,15 @@ CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT,INT ) RETURNS SETOF ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name; $$ LANGUAGE SQL STABLE; +CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_at_depth ( INT,INT ) RETURNS actor.org_unit AS $$ + SELECT a.* + FROM actor.org_unit a + WHERE id = ( SELECT FIRST(x.id) + FROM actor.org_unit_ancestors($1) x + JOIN actor.org_unit_type y + ON x.ou_type = y.id AND y.depth = $2); +$$ LANGUAGE SQL STABLE; + CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS $$ SELECT * FROM actor.org_unit_ancestors($1) @@ -110,6 +119,10 @@ CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor. FROM actor.org_unit_descendants($1); $$ LANGUAGE SQL STABLE; +CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT, INT ) RETURNS SETOF actor.org_unit AS $$ + SELECT * FROM actor.org_unit_full_path((actor.org_unit_ancestor_at_depth($1, $2)).id) +$$ LANGUAGE SQL STABLE; + CREATE OR REPLACE FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$ SELECT * FROM actor.org_unit_ancestors($1) -- 2.43.2