Message-ID: <3E53E892.6050805@incentre.net>
Date: Wed, 19 Feb 2003 13:26:58 -0700
From: Guy Fraser <guy@incentre.net>
Organization: J I Reasearch
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.1) Gecko/20021003
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Passing arrays
References: <3B663B41B350D311AEEF00A0C9254563201A73@VISION1>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Would it not be more reasonable to have array_dims return an int or int[]?

Has anyone ever seen an array that does not start at 1?

The other problem I find with array_dims returning text is when you have a 
multi-dimentional array like this IIRC;

array_dims('{{asd,fgh,jkl},{zxc,vbn,mlk}}') = '[1:2][1:3]'

Which appears to mean that there the data is a 2 element array of a 3 element 
array.

If the data was in an int array format like '{{1,2},{1,3}}' it would be dead 
easy to get the dimentions of the array without messy text parsing. It would 
be even better as '{2,3}' since a null element at the start of array is still 
counted as an element so all arrays start from 1. A fairly simple function 
could be made to "factor" all dimentions together to get a full sub_element 
count, ie. 2x3 = 6 ... .

I think I will update my array_size function to handle this, but that means my 
funtion has to deal with more messy text parsing to generate the int array for 
multi dimentional arrays. I have up until now only been working with single 
element arrays.

Here is an example of my array_size function for text arrays, I just tossed 
this together from what I could remember, so it may not be exactly the same as 
what I am using.

For V7.3 it should look somthing like this.

---%<...snip...
CREATE FUNCTION array_size(text[]) RETURNS int AS '
DECLARE
   array ALIAS FOR $1;
   dim int;
BEGIN
   SELECT INTO dim
     replace(split_part(array_dims(array),'':'',2),'']'','''')::int ;
-- that was the messy stuff
     IF dim IS NULL
     THEN
       dim := 0 ;
     END IF;
   RETURN dim;
END;
' LANGUAGE 'plpgsql';
---%<...snip...

For V7.2 it looked something like this, but it is more messy.

---%<...snip...
CREATE FUNCTION array_size(text[]) RETURNS int AS '
DECLARE
   array ALIAS FOR $1;
   dim int;
BEGIN
   SELECT INTO dim
     rtrim(ltrim(ltrim(array_dims($1),''[012345679''),'':''),'']'')::int ;
-- that was the messy stuff
     IF dim IS NULL
     THEN
       dim := 0 ;
     END IF;
   RETURN dim;
END;
' LANGUAGE 'plpgsql';
---%<...snip...

I dropped these into a test DB, created test table and they do work so, here 
are the results:

select *,array_size(destination) from size_test;

    alias   |        destination        | array_size
-----------+---------------------------+------------
  alias1    | {dest1}                   |          1
  alias2    | {dest2,dest1}             |          2
  alias3    | {dest3,dest4}             |          2
  alias4    | {dest3,dest4,dest5}       |          3
  alias5    | {dest6,dest7}             |          2
  alias6    | {dest3,dest7,dest4,dest5} |          4
  alias7    |                           |          0


I hope that this helps. You can over load the function by creating more of the 
same function but using different array types for the input.

IE. array_size(int[]) instead of array_size(text[]).


Guy

Michael Weaver wrote:
> There is a function array_dims(array) that returns the size of array.
> It's not THAT useful as it returns a string like '[1:2]' <-( 1 lower, 2 
> upper bound.)
> With a little bit of string processing you could get the size of the array.
> 
> 
>