On Jun 27, 2007, at 9:32 AM, Jaime Silvela wrote:
I've been looking around for this functionality:
Is it possible to use COPY with a file that doesn't reside in the
DB's filesystem? I know there is sort of a solution in using COPY
from stdin/ COPY to stdout, however that depends on calling the
psql command, which breaks the flow of control of the programs I'm
trying to write.
Here's the story: I have a Ruby on Rails application which sits on
server A, and connects to a Postgres Database running on B. Using
the regular flow, the rails code writes into various tables, and
then generates a table that needs to be exported into a file which
will be used as input into a separate program, a simulator, and
kept in the filesystem of the Rails server. Using the COPY command
would entail either of
a) Using COPY to generate the file on server B, then transfer to A
- but how? ftp? I want to reduce this type of coupling
b) Using COPY TO STDOUT from psql, called in the Rails code with a
backtick, then gathering the output and filing it. - but this
solution depends on having psql in the path of the Rails server,
reintroducing the server credentials, and from a programming point
of view is kind of ugly.
c) The Postgres driver for Rails tries to give an interface to the
COPY command using "raw_connection", "getline" and "endcopy", but
it's quite brittle, so I'm avoiding it altogether.
At the moment I'm avoiding those solutions, and instead get the
table into Rails space with a CURSOR on a SELECT, then simply write
the file in Rails, one line at a time. However, the tables I'm
writing are pretty big, and the performance is so much worse than
with COPY...
Any suggestions?
Thanks
Jaime
The way we usually handle situations similar to this is to use
network mounts of directories that are visible from both servers
using, say, nfs.
Erik Jones
Software Developer | Emma®
erik@xxxxxxxxxx
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
