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
