Search Postgresql Archives

Re: array/function question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux