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...
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
