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!