Re: [HACKERS] CSV hack

[Andrew Dunstan]

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
 



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
     joining column's datatypes do not match