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@fetter.org http://fetter.org/
phone: +1 510 893 6100    cell: +1 415 235 3778

Wed, 10 Dec 2003 15:05:34 -0800
David Fetter <david@fetter.org>
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>.

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


Wed, 10 Dec 2003 17:08:19 -0800
Joe Conway <mail@joeconway.com>
Re: CSV hack
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

Wed, 10 Dec 2003 17:54:59 -0800
David Fetter <david@fetter.org>
Re: [Dbdpg-general] 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
>  
>


Wed, 10 Dec 2003 23:01:46 -0500
Andrew Dunstan <andrew@dunslane.net>
Re: CSV hack
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

Wed, 10 Dec 2003 23:43:34 -0800
david@fetter.org (David Fetter)
Re: CSV hack