Re: Could someone help me fix my array_list function?

Guy Fraser wrote:
> 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

You didn't mention the version of PostgreSQL. If you're using < 7.3, good luck 
;-). If you are using 7.3, the following works:

DROP TABLE mail_aliases;
CREATE TABLE mail_aliases(  a_mailbox text,  a_destination text[]
);

INSERT INTO mail_aliases VALUES ('alias1', '{dest1}');
INSERT INTO mail_aliases VALUES ('alias2', '{dest2,dest1}');
INSERT INTO mail_aliases VALUES ('alias3', '{dest3,dest4}');
INSERT INTO mail_aliases VALUES ('alias4', '{dest3,dest4,dest5}');
INSERT INTO mail_aliases VALUES ('alias5', '{dest6,dest7}');
INSERT INTO mail_aliases VALUES ('alias6', '{dest3,dest7,dest4,dest5}');

CREATE TYPE mail_aliases_list_type AS (a_mailbox text, a_destination_el text);
CREATE OR REPLACE FUNCTION mail_aliases_list() RETURNS SETOF 
mail_aliases_list_type AS '
DECLARE  rec record;  retrec record;  low int;  high int;
BEGIN FOR rec IN SELECT a_mailbox, a_destination FROM mail_aliases LOOP  SELECT INTO low
replace(split_part(array_dims(rec.a_destination),'':'',1),''['','''')::int; SELECT INTO high
replace(split_part(array_dims(rec.a_destination),'':'',2),'']'','''')::int;
  FOR i IN low..high LOOP   SELECT INTO retrec rec.a_mailbox, rec.a_destination[i];   RETURN NEXT retrec;  END LOOP;
ENDLOOP; RETURN;
 
END;
' LANGUAGE 'plpgsql';

regression=# SELECT a_mailbox, a_destination_el FROM mail_aliases_list(); a_mailbox | a_destination_el
-----------+------------------ alias1    | dest1 alias2    | dest2 alias2    | dest1 alias3    | dest3 alias3    |
dest4alias4    | dest3 alias4    | dest4 alias4    | dest5 alias5    | dest6 alias5    | dest7 alias6    | dest3 alias6
  | dest7 alias6    | dest4 alias6    | dest5
 
(14 rows)


HTH,

Joe