CSV hack

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&gt;.

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&gt;.

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:

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;
RETURN quoted_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