Export an array into a CSV-file in PL/pgSQL

Question

I have a function, which RETURNS SETOF text[]. Sample result of this function:

{080213806381,"personal data1","question 1",answer1,"question 2",answer2,"question 3","answer 3"}
{080213806382,"personal data1","question 1",answer1,"question 2",answer2,"question 3","answer 3"}

I'm forming each row with a statement like:

resultRow := array_append(resultRow, fetchedRow.data::text);

and then:

RETURN NEXT resultRow;

And here's my COPY command:

COPY( 
SELECT myFunction()
) TO 'D:\test_output.csv' WITH (FORMAT 'csv', DELIMITER E',', HEADER false)

And I have a couple of problems:

Regardless the fact that values are appended to the array in the same way, some of them are double-quoted and some of them are not. This somehow depends on a presence of space character in a value. Look, for instance, at the 1st element of the array or at the answer2 and "answer 3" in each row. I want some unified behavior.
After exporting in to CSV with COPY command I'm getting the same rows with all these curly braces at the beginning and the end. I dont want them in CSV.

What can I do to solve these issues?

Does every row returned have the same number of columns? Or do they vary? — Craig Ringer, Sep 16 '15 at 11:46
Use `select * from myfunction()` instead of `select myfunction()` — , Sep 16 '15 at 11:50
It's strings with spaces inside them that's quoted. But why should it matter as long as it can be read back into the database? — e4c5, Sep 16 '15 at 11:50
@e4c5 It matters for the end user of CSV. Quotation principles should be the same for all the columns (preferably, there shouldn't be any redundant quotes at all) — kumade, Sep 16 '15 at 11:58
a) quoting strings with spaces and not quoting those without spaces is a standard practice in many CSV flavours and many csv readers can handle them too — e4c5, Sep 16 '15 at 12:01
You're mixing up the quoting PostgreSQL uses for its array literals with the only vaguely similar quoting used in CSV. They are not the same thing. Array literals are not CSV and you can't really expect to use them as CSV rows. — Craig Ringer, Sep 16 '15 at 12:11

Craig Ringer · Accepted Answer · 2015-09-16T12:09:56.783

You wish to export rows of varying numbers of columns. You're producing a set of arrays, but from there want to produce a CSV file.

The immediate issue - array literals aren't CSV

Your function returns text[] literals, i.e. PostgreSQL array literals.

These are not CSV as commonly recognised. They're comma-separated, yes, but they follow different syntax rules. You can't reliably treat an array literal as a CSV row or vice versa.

Don't attempt to just chop the delimiting {...} off and treat the array literal as a CSV row.

COPY won't work well or at all

COPY is not going to work well for you. It's designed to handle relations, i.e. uniform sets of structured rows where each column is of a well defined type and each row has the same number of columns.

You could redefine your function to return a setof record and pad your records with nulls to always be the same width, but it'll be pretty ugly and limited, plus the CSV will then incorporate the nulls.

What COPY will do is export a single column CSV containing array literals in a single CSV field. This certainly will not be what you want.

Solution 1: Export client-side

You might be better off doing this on the client side, via a script or program to generate the CSV. Have the program receive the set of arrays and then write it to CSV via a suitable library, like Python's csv module. Choose a client scripting language where the PostgreSQL driver understands arrays and can transform them to arrays in the language's format - again, like psycopg2 for Python.

e.g. given dummy function:

CREATE OR REPLACE FUNCTION get_rows() RETURNS setof text[] AS $$
VALUES
('{080213806381,"personal data1","question 1",answer1,"question 2",answer2,"question 3","answer 3"}'::text[]),
('{080213806382,"personal data1","question 1",answer1,"question 2",answer2,"question 3","answer 3","q4","a4"}'::text[])
$$ LANGUAGE SQL;

a client script could be as simple as:

#!/usr/bin/env python
import psycopg2
import csv

with psycopg2.connect('dbname=craig') as conn:
    curs = conn.cursor()

    with open("test.csv","w") as csvfile:
        f = csv.writer(csvfile)

        curs.execute("SELECT * FROM get_rows()")

        for row in curs:
            f.writerow(row[0])

Solution 2: Export CSV directly from a procedure

Alternately, if the CSV document isn't too big, you could produce the entire CSV in a single procedure, perhaps using plpythonu and the csv module, or a similar CSV library for your preferred procedural language. Because the whole CSV document must be accumulated in memory this won't scale to very very large documents.

So, in short, i can't use COPY to export arrays into CSV. I'll try the way with determining the maximal possible columns count and padding empty fields with nulls, because Your other sollutions aren't applicable for me. — kumade, Oct 14 '15 at 07:50

score 0 · Answer 2 · answered Sep 16 '15 at 12:27

Using text array as result format is wrong idea - a text array format is not simply convertible with CSV format. Return table instead

CREATE OR REPLACE FUNCTION foo()
RETURNS TABLE(c1 text, c2 text, c3 text, c4 text, c5 text, c6 text, c7 text, c8 text)
AS $$
  VALUES('080213806381','personal data1','question 1','answer1','question 2','answer2','question 3','answer 3'),
        ('080213806382','personal data1','question 1','answer1','question 2','answer2','question 3','answer 3');
$$ LANGUAGE sql;

postgres=# COPY (SELECT * FROM foo()) TO stdout CSV;
080213806381,personal data1,question 1,answer1,question 2,answer2,question 3,answer 3
080213806382,personal data1,question 1,answer1,question 2,answer2,question 3,answer 3
Time: 1.228 ms

Export an array into a CSV-file in PL/pgSQL

2 Answers2

The immediate issue - array literals aren't CSV

COPY won't work well or at all

Solution 1: Export client-side

Solution 2: Export CSV directly from a procedure

Linked