Could someone help me fix my array_list function? - Mailing list pgsql-sql

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 int2AS
'select int2(ltrim(rtrim(rtrim(array_dims($1),\']012345679\'),\':\'),\'[\')) 
AS RESULT;'LANGUAGE sqlWITH (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 int2AS 'select 
int2(rtrim(ltrim(ltrim(array_dims($1),\'[012345679\'),\':\'),\']\')) AS RESULT;'LANGUAGE sqlWITH (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...