How to create a view that explodes a csv field into multiple rows?

Question

I have a table structured as:

create table a (
   a bigint primary key,
   csv varchar(255)
)

I would like to be able to query a view ("b") such that:

select * from b;

Yields something like:

a | b
------
1 | A
1 | B
1 | C

For the case where the initial table has one row of data (1, 'A,B,C').

Is this possible with a postgres view?

Answer 1

In Postgres 8.4 (and I believe 8.3 as well), the regexp_split_to_table is available. This would work, however, I needed something for 8.1 as well.

This seems to work ok:

create or replace function split_xmcuser_groups_to_tuples() RETURNS SETOF RECORD AS $$
DECLARE
    r a%rowtype;
    strLen integer;
    curIdx integer;
    commaCnt integer;
    curCSV varchar;
BEGIN
    curIdx := 1;
    commaCnt := 1;
    FOR r IN SELECT * FROM a
    LOOP
        strLen := char_length(r.csv);
        while curIdx <= strLen LOOP
            curIdx := curIdx + 1;
            if substr(r.csv, curIdx, 1) = ',' THEN
                commaCnt := commaCnt + 1;
            END IF;
        END LOOP;
        curIdx := 1;
        while curIdx <= commaCnt LOOP
            curCSV := split_part(r.csv, ',', curIdx);
            if curCSV != '' THEN
                RETURN QUERY select r.a,curCSV;
            END IF;
            curIdx := curIdx + 1;
        END LOOP;
    END LOOP;
    RETURN;
END
$$ LANGUAGE 'plpgsql';

(and yes, I know about the performance implications and reasons not to do this)

Answer 2

I would say that this should be handled in application code if possible. Since it is a CSV field, I'm assuming that the number of entries is small, say, <1000 per database row. So, the memory and cpu costs wouldn't be prohibitive to split on commas and iterate as needed.

Is there a compelling reason this has to be done in postgres instead of the application? If so, perhaps you could write a psql procedure to fill a temporary table with the results of splitting each row. Here's an example of using comma splitting: http://archives.postgresql.org/pgsql-novice/2004-04/msg00117.php