copy command in postgresql to append data

I am executing copy command from a function

execute 'copy (select * from tableName) to ''/tmp/result.txt'''

It works fine. But, tableName is dynamic and will be having more tables (iterating in a loop). result.txt is having only the last iteration (table) data.

I could do, if I had to, via the command line with \o or >> by appending to a file. But, I have to use the function.

I read http://shuber.io/reading-from-the-filesystem-with-postgres/. It suggests a solution using temporary tables. Can I do something similar, but without temp tables? (I must avoid archive logs). Will creating a temp table create archive logs?

Is there any way to do appending with copy command in a function?

Assuming a Unix system
with PostgreQSL 9.3 or newer

Use the PROGRAM clause with cat as the program.

In the context of a loop in the plpgsql language, where tableName would be a variable, the invocation would typically look like:

execute format(
 'copy (select * from %I) to PROGRAM ''cat >>/tmp/result.txt''',
       tableName);

This would append the results to the end of /tmp/result.txt instead of overwriting the file.

Or if tableName is not a variable but directly the name of an actual table, the simpler following invocation will do:

COPY tableName to PROGRAM 'cat >>/tmp/result.txt';

with older versions of PostgreSQL

A named pipe can be used by a separate process to concatenate results.
Example:

$ mkfifo /tmp/pgfifo
$ while true; do cat /tmp/pgfifo >>/tmp/result.txt; done

This last command will block. Let it run until all the results are accumulated, then it can be terminated with ^C or kill.

In SQL, the fifo has to be fed with:

COPY tableName to '/tmp/pgfifo'

or if the context is plpgsql code where tableName is a variable:

execute 'COPY ' || quote_ident(tableName) || ' TO ''tmp/pgfifo''';

StackExchange Database Administrators Q#149745, answer score: 8