From: Guy Fraser <guy@incentre.net>
To: pgsql-sql@postgresql.org
Subject: Re: Passing arrays
Date: Wed, 19 Feb 2003 13:26:58 -0700
Message-ID: <3E53E892.6050805@incentre.net> (raw)
References: <3B663B41B350D311AEEF00A0C9254563201A73@VISION1>
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.
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: guy@incentre.net
Subject: Re: Passing arrays
In-Reply-To: <3E53E892.6050805@incentre.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox