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;
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
--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 cell: +1 415 235 3778