Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 5864D476550
	for <pgsql-sql@postgresql.org>; Mon, 20 Jan 2003 18:29:05 -0500 (EST)
Received: from joeconway.com (unknown [63.210.180.150])
	by postgresql.org (Postfix) with ESMTP id 93C074763A4
	for <pgsql-sql@postgresql.org>; Mon, 20 Jan 2003 18:29:04 -0500 (EST)
Received: from [206.19.64.3] (account jconway HELO joeconway.com)
	by joeconway.com (CommuniGate Pro SMTP 4.0.4)
	with ESMTP-TLS id 1571108; Mon, 20 Jan 2003 16:03:17 -0800
Message-ID: <3E2C85E3.3040702@joeconway.com>
Date: Mon, 20 Jan 2003 15:27:31 -0800
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.2.1) Gecko/20021130
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Guy Fraser <guy@incentre.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Could someone help me fix my array_list function?
References: <3E2C44F3.8010602@incentre.net>
In-Reply-To: <3E2C44F3.8010602@incentre.net>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/308
X-Sequence-Number: 11173

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;
  END LOOP;
  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    | dest4
  alias4    | dest3
  alias4    | dest4
  alias4    | dest5
  alias5    | dest6
  alias5    | dest7
  alias6    | dest3
  alias6    | dest7
  alias6    | dest4
  alias6    | dest5
(14 rows)


HTH,

Joe