[Gmod-schema-cmts] schema/chado/modules/phylogeny/functions phylo.plpgsql,NONE,1.1 phylo.sqlapi,NONE,1.1

[Chris M. <cmu...@us...>]

Update of /cvsroot/gmod/schema/chado/modules/phylogeny/functions
In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv6128/functions

Added Files:
	phylo.plpgsql phylo.sqlapi 
Log Message:
depth and height functions

--- NEW FILE: phylo.sqlapi ---
COMMENT ON FUNCTION phylonode_depth(INT) IS 'returns as a FLOAT the
depth of the node with a particular phylonode_id - depth is defined as
the sum of distances from the node to the root';

COMMENT ON FUNCTION phylonode_height(INT) IS 'returns as a FLOAT the
height of the node with a particular phylonode_id - height is defined as
the maximum total distance from the node to the leaves';


--- NEW FILE: phylo.plpgsql ---

CREATE OR REPLACE FUNCTION phylonode_depth(INT)
 RETURNS FLOAT AS
 'DECLARE  id    ALIAS FOR $1;
  DECLARE  depth FLOAT := 0;
  DECLARE  curr_node phylonode%ROWTYPE;
  BEGIN
   SELECT INTO curr_node *
    FROM phylonode 
    WHERE phylonode_id=id;
   depth = depth + curr_node.distance;
   IF curr_node.parent_phylonode_id IS NULL
    THEN RETURN depth;
    ELSE RETURN depth + phylonode_depth(curr_node.parent_phylonode_id);
   END IF;
 END
'
LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION phylonode_height(INT)
 RETURNS FLOAT AS
'
  SELECT coalesce(max(phylonode_height(phylonode_id) + distance), 0.0)
    FROM phylonode
    WHERE parent_phylonode_id = $1
'
LANGUAGE 'sql';