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
