Re: comma separated value splitting

due to situations beyond my control i have a field that holds comma separated
email addresses.

create table filters (name varchar(64), filter text);
insert into filters values ('test', 'test1(at)test(dot)com, test2(at)test(dot)com,
test3(at)test(dot)com');

filter is the field that holds the email addresses.

how do i make postgres split for me the email addresses and return me a table
so that for 'test' I get:

name | email
---------------------
test | test1(at)test(dot)com
test | test2(at)test(dot)com
test | test3(at)test(dot)com

any help appreciated

- Martin -

Martin Atukunda wrote:
> how do i make postgres split for me the email addresses and return me a table
> so that for 'test' I get:
>
> name | email
> ---------------------
> test | test1(at)test(dot)com
> test | test2(at)test(dot)com
> test | test3(at)test(dot)com

You didn't mention your Postgres version. If it's 7.4.x, this will work:

create table filters (name varchar(64), filter text);
insert into filters values ('test', 'test1(at)test(dot)com, test2(at)test(dot)com,
test3(at)test(dot)com');

CREATE TYPE filters_type AS (name varchar(64), email text);
CREATE OR REPLACE FUNCTION filters_list()
RETURNS SETOF filters_type AS '
DECLARE
rec record;
retrec filters_type;
low int;
high int;
BEGIN
FOR rec IN SELECT name, string_to_array(filter,'','') AS
filter_array
FROM filters LOOP
low := array_lower(rec.filter_array, 1);
high := array_upper(rec.filter_array, 1);
FOR i IN low..high LOOP
retrec.name := rec.name;
retrec.email := btrim(rec.filter_array[i]);
RETURN NEXT retrec;
END LOOP;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';

regression=# SELECT name, email FROM filters_list();
name | email
------+----------------
test | test1(at)test(dot)com
test | test2(at)test(dot)com
test | test3(at)test(dot)com
(3 rows)

HTH,

Joe