Re: COPY TABLE TO

On Mon, Dec 22, 2003 at 10:35:08AM -0000, Paul Punett wrote:
>
> I need to write a tab separated text file such that the first row contains
> number of records in the table.

Whether COPY does what you want may depend on what you want to do with
special characters. If your table contains strings with strange
characters like newline, tab, non-ASCII characters etc. then COPY will
replace them by escape sequences. I guess in most cases you won't
have any problems with this, but it's a thing to keep in mind.

> I need count as the first record? Any suggestions please ?
> The documentation says indexing does not affect copy order.

Rather than tricking COPY into generating your file format, you may
want to use COPY TO STDOUT and do some processing on the lines you
get from that.

> I am developing on C++ with PostGre on windows. I need to port to Linux
> later. Any suggestions on linking C++ code to PostGre (queries & functions)

Try libpqxx (http://pqxx.tk/). Use the tablereader class to read raw
lines from your table and write them to your file. Something like this
should do the trick:

connection c(myoptions);
transaction<serializable> t(c);
result count = t.exec("select count(*) from " + mytable);
myfile << count[0][0] << endl;
tablereader reader(t, mytable);
string line;
while (reader.get_raw_line(line)) myfile << line << endl;

This may be a bit slower than a direct COPY because the data has to go
through your program rather than directly to the file, but it gives you
more control over the file's format.

(I used a serializable transaction here because otherwise rows may be
added or deleted by somebody else at just the wrong moment, e.g. after
the count but before we read the table)

Jeroen

PS - It's Postgres or PostgreSQL, not PostGre!