I previously asked about determining array sizes. I got a couple of direct replies..
they didn't fit my need.
I am trying to get some code finished so I hacked a couple of functions together with
plpgsql to
solve my immediate problem. They make the assumption that NULL's are not allowed in
the array.
In the future I will dig into the array code and add the methods I need in 'CA'. I
just won't have time until after July.
So, I thought I would post them and you can use them.. or not :-)
I figured if I posted something and someone had a better way.. they would reply.
- james -
/*
create a custom datatype that is an array of text.
use the builtin conversion routines.
*/
CREATE TYPE textarray (
INPUT = array_in,
OUTPUT = array_out,
INTERNALLENGTH = VARIABLE,
ELEMENT = text,
DEFAULT = '{}'
);
/*
try to figure out the number of elements in this
text array. This assumes that NULL elements
are not allowed in the array and mark the end
of the array.
USAGE:
size(textarray)
*/
CREATE FUNCTION size(textarray)
RETURNS int4
AS '
DECLARE
data_array ALIAS FOR $1;
array_element text;
counter int4;
BEGIN
-- set the counter
counter := 0;
-- loop until we terminate
WHILE true LOOP
-- get the element from the array
array_element := data_array[counter + 1]; -- 1 based arrays
-- exit the loop if no more data
IF (array_element IS NULL) THEN
EXIT; -- exit the loop
END IF;
-- increment the counter
counter := counter + 1;
END LOOP;
RETURN counter;
END;
' LANGUAGE 'plpgsql';
/*
try to figure out the number of elements in this
integer array. This assumes that NULL elements
are not allowed in the array and mark the end
of the array.
USAGE:
size(_int4)
*/
CREATE FUNCTION size(_int4)
RETURNS int4
AS '
DECLARE
data_array ALIAS FOR $1;
array_element int4;
counter int4;
BEGIN
-- set the counter
counter := 0;
-- loop until we terminate
WHILE true LOOP
-- get the element from the array
array_element := data_array[counter + 1]; -- 1 based arrays
-- exit the loop if no more data
IF (array_element IS NULL) THEN
EXIT; -- exit the loop
END IF;
-- increment the counter
counter := counter + 1;
END LOOP;
RETURN counter;
END;
' LANGUAGE 'plpgsql';
