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?
Best Answer
I think you cannot (easily) do that (at least on your PostgreSQL version, see Daniel's answer). What you definitely can, however, is changing your approach, and do a
UNION ALL
from all those tables in the query part of yourCOPY
. This means there is no looping, but you have to construct your query from the collected table names.The result would look like
Notes:
psql
function.psql
is a client to PostgreSQL. It has built in commands (basically everything starting with\
), however.