Received: from janus.hosting4u.net (janus.hosting4u.net [209.15.2.37]) by hub.org (8.10.1/8.10.1) with SMTP id e5H9dXM94824 for ; Sat, 17 Jun 2000 05:39:33 -0400 (EDT) Received: (qmail 12888 invoked from network); 17 Jun 2000 09:39:29 -0000 Received: from aquarius.hosting4u.net (HELO jfca.com) (209.15.2.40) by mail-gate.hosting4u.net with SMTP; 17 Jun 2000 09:39:29 -0000 Received: from epsilon3.jfca.com ([12.22.49.216]) by jfca.com ; Sat, 17 Jun 2000 04:39:27 -0500 Message-Id: <4.3.1.2.20000617022916.03ae5bc0@jfca.com> X-Sender: jamesc@jfca.com X-Mailer: QUALCOMM Windows Eudora Version 4.3.1 Date: Sat, 17 Jun 2000 02:39:07 -0700 To: pgsql-sql@postgresql.org From: James Carpenter Subject: Determining Array size. (HACK) Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="=====================_198550187==_" X-Archive-Number: 200006/168 --=====================_198550187==_ Content-Type: text/plain; charset="us-ascii" 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 - --=====================_198550187==_ Content-Type: text/plain; charset="us-ascii" /* 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'; --=====================_198550187==_ Content-Type: text/plain; charset="us-ascii" --=====================_198550187==_--