Convert CSV text fields into text[] arrays

The last item on my list of things to do before releasing the pagila sample database was to finish converting the special_features column. Originally I just ported it as text, but what I really wanted was to make it a text[], thereby giving us an array field to work with in the db. Sitting there looking at the data, I was concerned I was actually going to have to write a script to convert it all, but then I thought that it would be fairly possibly that you could do this in plpgsql, and maybe someone else had already done this. After a quick google search turned up nothing, I turned to #postgresql for help. Luckily horar had just what I needed:

CREATE OR REPLACE FUNCTION dParseCSV

(

    aText TEXT

)

    RETURNS TEXT[] AS $$

DECLARE

    vLength INTEGER;

    vList TEXT[];

    vRest TEXT;

    vNext TEXT;

BEGIN

    IF POSITION('"' IN aText) = 0

    THEN

        RETURN STRING_TO_ARRAY(aText,',');

    END IF;

    vRest := REPLACE(aText,'""',E'');

    LOOP

        IF POSITION('"' IN vRest) = 1

        THEN

            vLength := POSITION('"' IN SUBSTRING(vRest FROM 2));

            IF vLength = 0

            THEN

                vNext := vRest;

                vRest := NULL;

            ELSE

                vNext := SUBSTRING(vRest FROM 2 FOR vLength - 1);

                vRest := SUBSTRING(vRest FROM vLength + 3);

            END IF;

        ELSE

            vLength := POSITION(',' IN vRest);

            IF vLength = 0

            THEN

                vNext := vRest;

                vRest := NULL;

            ELSE

                vNext := SUBSTRING(vRest FOR vLength - 1);

                vRest := SUBSTRING(vRest FROM vLength + 1);

            END IF;

        END IF;

        vNext := REPLACE(vNext,'','"');

        IF vList ISNULL

        THEN

            vList := ARRAY[vNext];

        ELSE

            vList := vList || ARRAY[vNext];

        END IF;

        EXIT WHEN vRest ISNULL;

    END LOOP;

    RETURN vList;

END;

$$ LANGUAGE plpgsql IMMUTABLE;

With this I was able to issue a single command to update my database schema and the data: ALTER TABLE film ALTER COLUMN special_features TYPE text[] using dParseCSV(special_features); cool beans!