From: James Carpenter <jamesc@jfca.com>
To: pgsql-sql@postgresql.org
Subject: Determining Array size. (HACK)
Date: Sat, 17 Jun 2000 02:39:07 -0700
Message-ID: <4.3.1.2.20000617022916.03ae5bc0@jfca.com> (raw)
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';
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: jamesc@jfca.com
Subject: Re: Determining Array size. (HACK)
In-Reply-To: <4.3.1.2.20000617022916.03ae5bc0@jfca.com>
References: <4.3.1.2.20000617022916.03ae5bc0@jfca.com>
* 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