Обсуждение: 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@test.com, test2@test.com,
test3@test.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@test.com
test | test2@test.com
test | test3@test.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@test.com
> test | test2@test.com
> test | test3@test.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@test.com, test2@test.com,
test3@test.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@test.com
  test | test2@test.com
  test | test3@test.com
(3 rows)

HTH,

Joe