Re: array/function question

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
>