Re: CSV hack

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
>
>