On Wed, 11 Aug 2004, [iso-8859-1] Clodoaldo Pinto Neto wrote:
> I should have explained it better:
>
> Why don't I just insert each line in the table and let "copy to" do its
> thing inserting a new line char (lf) at the end of each line?
> Because I need the file lines to be in a certain order. Copy to will
> copy the lines in its own order.
> Until now I'm inserting html pages in a single line table without a lf
> and copying then to files. Here is the result:
> http://planeta.terra.com.br/informatica/kakaostats/
> http://planeta.terra.com.br/informatica/kakaostats/t13802.html
> Right click the page and click view source and there it is, a single
> giant line.
> The problem with mixing html and pl/pgsql is that it becomes a big
> mess. It is very hard to evolve the code and understand it.
> So I choose to not output html anymore but cvs text and build the page
> with php. Advantages: much simpler pl/pgsql code and upload size 80%
> smaller.
> Lines finishing with a lf are much easier to parse in php (fgetcsv).
> This and the need to have then ordered is why i need to insert lfs in
> the single line table from which to copy to file.
> I have solved it with a simple python script but it is one more step
> and is not fail safe. Where there would be a lf I insert the string
> "newLine". Then in the python script I change it to an actual lf char.
> But in the remote possibility that there is or there will be this
> string in the database (600+ thousand distinct user names) then the
> output file will be wrong.
> Also it is one more piece of software to be mantained.
I think in general you might be better off either writing functions in an
untrusted language that actually do what you want with the files or a
"client" app that connects to the database and gets the data you want and
puts it into a file. I don't think COPY TO is really meant as a general
purpose writing arbitrary content into a file tool.