Martin Atukunda wrote:
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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html