Friday, December 09, 2005

A new variant on stragg - the ordered aggregate

In a previous post, I included an example of a variant of Tom Kyte's STRAGG (string aggregate) function that ordered the elements alphabetically. Here I'm showing one where the elements can be ordered differently. Basically, for every string to be aggregated, you need to pass in the value itself and its order position. It doesn't test to see if that order position is already taken, so if you have two values in sort position 3, one of them will be lost. Also, the order position should be an integer.

Firstly, create the necessary types. The first two are pretty obvious, the third is the aggregate type:
`create or replace type sort_element is object   (sort_seq number, sort_value varchar2(2000))./create type tab_sort is table of sort_element./create or replace type str_ord_agg_type as object(   t_sort tab_sort,   static function        ODCIAggregateInitialize(sctx IN OUT str_ord_agg_type )        return number,   member function        ODCIAggregateIterate(self IN OUT str_ord_agg_type ,                             value IN sort_element )        return number,   member function        ODCIAggregateTerminate(self IN str_ord_agg_type,                               returnValue OUT  varchar2,                               flags IN number)        return number,   member function        ODCIAggregateMerge(self IN OUT str_ord_agg_type,                           ctx2 IN str_ord_agg_type)        return number,   member function        ODCIAggregateDelete(self IN OUT str_ord_agg_type,                            value in sort_element)        return number)./`

Then the type body for the aggregation operation
`create or replace type body str_ord_agg_typeis--static function ODCIAggregateInitialize    (sctx IN OUT str_ord_agg_type)return numberisbegin    sctx := str_ord_agg_type( null );    --Initialize the sortage part    sctx.t_sort := tab_sort();    return ODCIConst.Success;end;--member function ODCIAggregateIterate     (self IN OUT str_ord_agg_type,      value IN sort_element )return numberisbegin    --Extend the array and add the new element.    t_sort.extend;    t_sort(t_sort.last) := value;    return ODCIConst.Success;end;--member function ODCIAggregateTerminate          (self IN str_ord_agg_type,           returnValue OUT varchar2,           flags IN number)return numberis  type tab_char is table of varchar2(4000) index by pls_integer;  t_char tab_char;  v_ind number;  v_out_line varchar2(4000);begin  --Store the elements in the array using the sort seq  v_ind := t_sort.first;  while v_ind is not null loop    t_char(t_sort(v_ind).sort_seq) := t_sort(v_ind).sort_value;    v_ind := t_sort.next(v_ind);  end loop;  --  --Go through in sort seq order, moving elements to the output string  v_ind := t_char.first;  while v_ind is not null loop    v_out_line := v_out_line||t_char(v_ind)||',';    v_ind := t_char.next(v_ind);  end loop;  --Remove last comma  v_ind := length(nvl(v_out_line,',')) -1;  returnValue := (substr(v_out_line,1,v_ind));  return ODCIConst.Success;end;--member function ODCIAggregateDelete      (self IN OUT str_ord_agg_type,       value in sort_element)return numberis  v_point number;begin  v_point := t_sort.first;  while v_point is not null loop    if  t_sort(v_point).sort_value = value.sort_value    and t_sort(v_point).sort_seq = value.sort_seq then      t_sort.delete(v_point);      return ODCIConst.Success;    end if;    v_point := t_sort.next(v_point);  end loop;  return ODCIConst.Success;end;--member function ODCIAggregateMerge         (self IN OUT str_ord_agg_type,           ctx2 IN str_ord_agg_type)return numberis  v_point number;begin  v_point := ctx2.t_sort.first;  while v_point is not null loop    t_sort.extend;    t_sort(t_sort.last) := ctx2.t_sort(v_point);    v_point := ctx2.t_sort.next(v_point);  end loop;  return ODCIConst.Success;end;end;./`

And finally the function :
`CREATE or replace  FUNCTION strordagg(input sort_element )  RETURN varchar2  PARALLEL_ENABLE AGGREGATE USING str_ord_agg_type;./grant execute on strordagg to public;select table_name, index_name,       strordagg(sort_element(column_position, column_name)) sortedfrom user_ind_columnsgroup by table_name, index_name/`

In 10G, we can use the COLLECT function using the same sort_element and tab_sort types, but with a much simpler function, though the select looks a little uglier.

`create or replace function sorted_agg    (p_tab_sort in tab_sort) return varchar2 is  type tab_char is table of varchar2(4000) index by pls_integer;  t_char tab_char;  v_ind number;  v_out_line varchar2(4000);begin  v_ind := p_tab_sort.first;  while v_ind is not null loop    t_char(p_tab_sort(v_ind).sort_seq) := p_tab_sort(v_ind).sort_value;    v_ind := p_tab_sort.next(v_ind);  end loop;  --  v_ind := t_char.first;  while v_ind is not null loop    v_out_line := v_out_line||t_char(v_ind)||',';    v_ind := t_char.next(v_ind);  end loop;  --Remove last comma  v_ind := length(v_out_line) -1;  return(substr(v_out_line,1,v_ind));end;./select index_name,       sorted_agg(cast(collect(sort_element(column_position, column_name)) as tab_sort)) sortedfrom all_ind_columnsgroup by index_name/`