Re: array/function question

2009/5/18 Joshua Berry <yoberi(at)gmail(dot)com>:
> 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 }
>

hello

try to SQL language

postgres=# create or replace function xx(anyarray, anyarray) returns
bool[] as $$
select array(select (select x = any(select y from unnest($2) g2(y)))
from unnest($1) g(x))
$$ language sql immutable;
CREATE FUNCTION
Time: 1,846 ms
postgres=# select xx(array[1,2,3,4,7], array[1,4,8,9]);
xx
-------------
{t,f,f,t,f}
(1 row)

if you know, so input are distinct and sorted, then you could to use function:

postgres=# create or replace function xy(anyarray, anyarray) returns
bool[] as $$
select array(select y is not null from unnest($1) g1(x) left join
unnest($2) g2(y) on x = y order by x);
$$ language sql immutable;
CREATE FUNCTION
Time: 2,666 ms
postgres=# select xx(array[1,2,3,4,7], array[1,4,8,9]); xx
-------------
{t,f,f,t,f}
(1 row)

regards
Pavel Stehule

regards
Pavel Stehule

> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>