COPY to/from non-local file - Mailing list pgsql-general
From | Jaime Silvela |
---|---|
Subject | COPY to/from non-local file |
Date | |
Msg-id | 46827500.9020800@bear.com Whole thread Raw |
Responses |
Re: COPY to/from non-local file
(Ted Byers <r.ted.byers@rogers.com>)
Re: COPY to/from non-local file (Erik Jones <erik@myemma.com>) Re: COPY to/from non-local file (Martijn van Oosterhout <kleptog@svana.org>) |
List | pgsql-general |
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. ***********************************************************************
pgsql-general by date: