Message-ID: <3E2C44F3.8010602@incentre.net>
Date: Mon, 20 Jan 2003 11:50:27 -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: Could someone help me fix my array_list function?
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Hi

I am trying to write a function to step through an array and output each value 
as a set {list}, I think.

This is what I want to do:

select attribute,array_list(values,1,sizeof(values)) as value from av_list;

Turn :
  attr6 | {val3,val7,val4,val5}

Into :
  attr6 | val3
  attr6 | val7
  attr6 | val4
  attr6 | val5

Below I have included my functions, a test query, a table definition
and some sample data.

If anyone already has a function to do this I would be elated.

Note: text array_dims(array[]); function existed on the machine I started this 
on, but does not exist on my machine at home. It outputs a text value like 
'[1:1]' when there is only one item in the array and '[1:6]' when there is six 
items. My functions expect that function to exist.


Any help would be apreciated.

Guy

The entire selection below can be pasted to a shell, it will create a test 
database "testdb" add plpgsql to the database then create the functions, and a 
populated table before running a test query.

---%<...Cut Here...
createdb testdb
createlang plpgsql testdb
echo "
--###Start of Functions###
-- Array dimension functions.
--
-- Throw away old version of function
DROP FUNCTION array_diml(text[]);
--
-- Return the start 'left' dimension for the text array.
CREATE FUNCTION array_diml(text[])
	RETURNS int2
	AS
'select int2(ltrim(rtrim(rtrim(array_dims($1),\']012345679\'),\':\'),\'[\')) 
AS RESULT;'
	LANGUAGE sql
	WITH (iscachable,isstrict)
;
--
-- Throw away old version of function
DROP FUNCTION array_dimr(text[]);
--
-- Return the end 'right' dimension for the text array.
CREATE FUNCTION array_dimr(text[])
	RETURNS int2
	AS 'select 
int2(rtrim(ltrim(ltrim(array_dims($1),\'[012345679\'),\':\'),\']\')) AS RESULT;'
	LANGUAGE sql
	WITH (iscachable,isstrict)
;
--
-- Throw away old version of function
DROP FUNCTION array_list(text[],smallint);
--
-- Iterate array and post results
CREATE FUNCTION array_list(text[],smallint)
RETURNS SETOF text AS '
DECLARE
   inarray ALIAS FOR $1;
   dim ALIAS FOR $2;
BEGIN
   FOR counter IN 1..dim LOOP
RAISE NOTICE ''Getting element % of %'',counter,inarray;
   RETURN inarray[counter];
  END LOOP;
END;
'
LANGUAGE 'plpgsql';
--###End of Functions###

--###Start of test query###
--
-- Get a list with each destination for each mailbox
SELECT a_mailbox,
   array_list(a_destination,
     array_dimr(a_destination))
FROM mail_aliases;
--###End of test query###

--###Start of table and sample data###
DROP TABLE mail_aliases;
CREATE TABLE mail_aliases(
   a_mailbox text,
   a_destination text[]
);
COPY mail_aliases FROM stdin USING DELIMITERS ':';
alias1:{dest1}
alias2:{dest2,dest1}
alias3:{dest3,dest4}
alias4:{dest3,dest4,dest5}
alias5:{dest6,dest7}
alias6:{dest3,dest7,dest4,dest5}
\.
--###End of table and sample data###

--###Start of test query###
--
-- Get a list with each destination for each mailbox
SELECT a_mailbox,
   array_list(a_destination,
     array_dimr(a_destination))
FROM mail_aliases;
--###End of test query###
" | psql testdb
---%<...Cut Here...