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
***********************************************************************
Bear Stearns is not responsible for any recommendation, solicitation,
offer or agreement or any information about any transaction, customer
account or account activity contained in this communication.
Bear Stearns does not provide tax, legal or accounting advice. You
should consult your own tax, legal and accounting advisors before
engaging in any transaction. In order for Bear Stearns to comply with
Internal Revenue Service Circular 230 (if applicable), you are notified
that any discussion of U.S. federal tax issues contained or referred to
herein is not intended or written to be used, and cannot be used, for
the purpose of: (A) avoiding penalties that may be imposed under the
Internal Revenue Code; nor (B) promoting, marketing or recommending to
another party any transaction or matter addressed herein.
***********************************************************************
