Forum OpenACS Development: Response to Reordering tree_sortkeys again

CREATE FUNCTION brk_class_dev_nodes__swap(integer, integer)
RETURNS integer as '
DECLARE
    p_node_id_1 alias for $1;
    p_node_id_2 alias for $2;
    v_sortkey_1 varbit;
    v_sortkey_2 varbit;
BEGI
    SELECT tree_sortkey INTO v_sortkey_1 FROM brk_class_dev_nodes WHERE node_id = p_node_id_1;

    SELECT tree_sortkey INTO v_sortkey_2 FROM brk_class_dev_nodes WHERE node_id = p_node_id_2;

    IF length(v_sortkey_1) != length(v_sortkey_2)
    THEN 
        RAISE EXCEPTION ''cannot reorder nodes at different tree levels'';
    END IF;

    -- swap the initial part of any sortkeys that match the 
    -- above (hence are the nodes themselves or child nodes)

    UPDATE brk_class_dev_nodes
    SET tree_sortkey = 
        CASE WHEN substring(tree_sortkey from 1 for length(v_sortkey_2)) = v_sortkey_2 THEN
                  v_sortkey_1 || substring(tree_sortkey from length(v_sortkey_1) + 1 for length(tree_sortkey))
             WHEN substring(tree_sortkey from 1 for length(v_sortkey_1)) = v_sortkey_1 THEN
                  v_sortkey_2 || substring(tree_sortkey from length(v_sortkey_2) + 1 for length(tree_sortkey)) 
        ELSE tree_sortkey end;

    RETURN 0; 
END;' language 'plpgsql';