From: Joe Conway <mail@joeconway.com>
To: Guy Fraser <guy@incentre.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Could someone help me fix my array_list function?
Date: Mon, 20 Jan 2003 15:27:31 -0800
Message-ID: <3E2C85E3.3040702@joeconway.com> (raw)
In-Reply-To: <3E2C44F3.8010602@incentre.net>
References: <3E2C44F3.8010602@incentre.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mail@joeconway.com, guy@incentre.net
Subject: Re: Could someone help me fix my array_list function?
In-Reply-To: <3E2C85E3.3040702@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox