Thread: CSV hack
Kind people, I've come up with yet another little hack, this time for turning 1-d arrays into CSV format. It's very handy in conjunction with the array_accum aggregate (can this be made a standard aggregate?) in <http://developer.postgresql.org/docs/postgres/xaggr.html>. Here 'tis... CREATE OR REPLACE FUNCTION csv(anyarray) RETURNS TEXT AS 'DECLARE in_array ALIAS FOR $1; temp_string TEXT; quoted_string TEXT; i INTEGER; BEGIN FOR i IN array_lower(in_array, 1)..array_upper(in_array, 1) LOOP IF in_array[i]::TEXT ~ ''"'' THEN temp_string := ''"'' || replace(in_array[i]::TEXT, ''"'', ''""'') || ''"''; ELSE temp_string:= in_array[i]::TEXT; END IF; IF i = array_lower(in_array, 1) THEN quoted_string :=temp_string; ELSE quoted_string := quoted_string || '','' || temp_string; END IF; END LOOP; RETURNquoted_string; END; ' LANGUAGE 'plpgsql'; Those DBD::Pg users among us who'd like to be able to bind_columns to postgresql arrays may have a leg up with Text::CSV_XS. Other middleware should be able to handle such things, too. :) Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 cell: +1 415 235 3778
David Fetter wrote: > I've come up with yet another little hack, this time for turning 1-d > arrays into CSV format. You mean like this (which is new in 7.4)? regression=# select array_to_string (array[1,2,3], ','); array_to_string ----------------- 1,2,3 (1 row) See: http://www.postgresql.org/docs/current/static/functions-array.html > It's very handy in conjunction with the array_accum aggregate (can > this be made a standard aggregate?) in > <http://developer.postgresql.org/docs/postgres/xaggr.html>. Early in the 7.4 dev cycle array_accum() was actually in cvs as a built-in C function (and it still does exist in PL/R as such). But toward the end of the cycle an objection was raised and it was removed. Search the archives in the May/June 2003 timeframe. Joe
On Wed, Dec 10, 2003 at 05:08:19PM -0800, Joe Conway wrote: > David Fetter wrote: > >I've come up with yet another little hack, this time for turning 1-d > >arrays into CSV format. > > You mean like this (which is new in 7.4)? > > regression=# select array_to_string (array[1,2,3], ','); > array_to_string > ----------------- > 1,2,3 > (1 row) Not quite. The CSV thing quotes the way you'd see in CSV files, as in SELECT csv(array['"1'::text, '2'::text, '3'::text]); csv -----------"""1",2,3 > See: > http://www.postgresql.org/docs/current/static/functions-array.html It's great, and I use it :) > >It's very handy in conjunction with the array_accum aggregate (can > >this be made a standard aggregate?) in > ><http://developer.postgresql.org/docs/postgres/xaggr.html>. > > Early in the 7.4 dev cycle array_accum() was actually in cvs as a > built-in C function (and it still does exist in PL/R as such). But > toward the end of the cycle an objection was raised and it was > removed. Search the archives in the May/June 2003 timeframe. Um, OK. It would be kinda handy, tho. Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 cell: +1 415 235 3778
You also need to quote values containing the separator. cheers andrew (who used to set creating CSV as a programming exercise - students almost never get it right) David Fetter wrote: >Kind people, > >I've come up with yet another little hack, this time for turning 1-d >arrays into CSV format. It's very handy in conjunction with the >array_accum aggregate (can this be made a standard aggregate?) in ><http://developer.postgresql.org/docs/postgres/xaggr.html>. > >Here 'tis... > >CREATE OR REPLACE FUNCTION csv(anyarray) RETURNS TEXT AS >'DECLARE > in_array ALIAS FOR $1; > temp_string TEXT; > quoted_string TEXT; > i INTEGER; >BEGIN > FOR i IN array_lower(in_array, 1)..array_upper(in_array, 1) > LOOP > IF in_array[i]::TEXT ~ ''"'' > THEN > temp_string := ''"'' || replace(in_array[i]::TEXT, ''"'', ''""'') || ''"''; > ELSE > temp_string := in_array[i]::TEXT; > END IF; > IF i = array_lower(in_array, 1) > THEN > quoted_string := temp_string; > ELSE > quoted_string := quoted_string || '','' || temp_string; > END IF; > END LOOP; > RETURN quoted_string; >END; >' LANGUAGE 'plpgsql'; > >Those DBD::Pg users among us who'd like to be able to bind_columns to >postgresql arrays may have a leg up with Text::CSV_XS. > >Other middleware should be able to handle such things, too. :) > >Cheers, >D > >
In article <3FD7EC2A.6020903@dunslane.net> you wrote: > > You also need to quote values containing the separator. Roight! Thanks for the heads-up :) And now, version 2. Cheers, D CREATE OR REPLACE FUNCTION csv(anyarray) RETURNS TEXT AS 'DECLARE in_array ALIAS FOR $1; temp_string TEXT; quoted_string TEXT; i INTEGER; BEGIN FOR i IN array_lower(in_array, 1)..array_upper(in_array, 1) LOOP IF in_array[i]::TEXT ~ ''[,"]'' THEN temp_string := ''"'' || replace(in_array[i]::TEXT, ''"'', ''""'') || ''"''; ELSE temp_string:= in_array[i]::TEXT; END IF; IF i = array_lower(in_array, 1) THEN quoted_string :=temp_string; ELSE quoted_string := quoted_string || '','' || temp_string; END IF; END LOOP; RETURNquoted_string; END; ' LANGUAGE 'plpgsql'; -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 cell: +1 415 235 3778 When a man tells you that he got rich through hard work, ask him: 'Whose?' Don Marquis, quoted in Edward Anthony, O Rare Don Marquis