hi, you should use something similar to 'merge sort' but only if your input is sorted (m_bx expects this) if your subjects (numbers) are not going beyond a certain limit eg(65535) take up an array and filter you can generate a poly for array B's roots, and calculate A's points -where it's 0, then the B array have the value ;))) writing the function in C is not so easy but it will be fast ;) create or replace function m_bx(a integer[],b integer[]) returns boolean[] as $BODY$ declare res boolean[]; declare i integer; declare j integer; declare la integer; declare lb integer; begin i=1; j=1; la=array_upper(a,1); lb=array_upper(b,1); loop if i>la then exit; end if; if (j<=lb and a[i] = b[j]) then res[i]=true; else res[i]=false; end if; if(b[j]<a[i]) then j=j+1; else i=i+1; end if; end loop; return res; end; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE COST 100; select m_bx('{1,2,4,5}','{1,5,6}'); Joshua Berry wrote: > Hello All, > > I'm trying to optimize a few slow queries and helper functions, and have > found a poor performing function. To improve performance, I'd like to > create a function that does the following: > > > Inputs: > A: an array of integers. for example: { 1, 2, 3, 4, 7 } > B: an array of integers. for example: { 1, 4, 8, 9 } > > Returns > C: an array of bools the same dimensions as Array A. In this example: { > true, false, false, false, true, false } > > Effectively, this function would use Array A as a set of boolean tests > to exercise on Array B. The result array will have the save number of > elements as array A. > > What I lack is the knowledge of how to > 1. index and compare arrays when their input size is not known. (I only > know how to use hardcoded indexes like A[1], B[2], etc. > 2. To use control structures for recursion/looping. I've read > http://www.postgresql.org/docs/8.3/interactive/plpgsql-control-structures.html but > still not sure how to apply the grammar to arrays data types. > > If there is a builtin array function that achieves this, that would be > good to know as well. > > Cheers, > > -Joshua > > Joshua Berry > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general