I found a temporary work-around where I ask PostgreSQL to save the output in a file instead of stdout...
>> sql = "COPY (select * from users) TO '/tmp/file.csv' WITH CSV HEADER;" => "COPY (select * from users) TO '/tmp/file.csv' WITH CSV HEADER;" >> ActiveRecord::Base.connection.execute(sql) SQL (0.2ms) SET client_min_messages TO 'panic' SQL (0.1ms) SET client_min_messages TO 'notice' SQL (2.6ms) COPY (select * from users) TO '/tmp/file.csv' WITH CSV HEADER; => #<PGresult:0x2650188> >> csv_string = File.read '/tmp/file.csv' => "email,fname,lname,created_at \[email protected],Foo,Fooster, 2009-07-07 17:00:41.929865\[email protected],Bar,Barbie,2009-07-01 20:31:08.659965\[email protected],User,User,2009-07-07 20:33:53.293606\[email protected],Admin,Admin,2009-07-07 20:33:53.760538\n" This is not ideal, but it's a start. Any other ideas? On Jul 10, 4:27 pm, gsterndale <[email protected]> wrote: > Thanks Philip, > > The Postgres gem rdoc hasn't been much help. All of the PGresult > instance methods that retrieve values require a tuple number. My > PGresult has a number of fields, but not tuples (rows). > > This query (when run in psql) returns a large block of text. For > example: > email,fname,lname,created_at > [email protected],Foo,Fooster,2009-07-07 17:00:41.929865 > [email protected],Bar,Barbie,2009-07-01 20:31:08.659965 > [email protected],User,User,2009-07-07 20:33:53.293606 > [email protected],Admin,Admin,2009-07-07 20:33:53.760538 > > I agree 100% that using FasterCSV and ActiveRecord to pull the data is > much more portable (and elegant). In fact, that's how I'm doing it > now. However, this is a huge dataset that is causing server timeouts > and hogs memory. I'm investigating csv generation in Postgres as it > takes a fraction of the time and resources because each object isn't > getting instantiated. > > Any thoughts? > > On Jul 10, 4:07 pm, Philip Hallstrom <[email protected]> wrote: > > > > I'm using ActiveRecord's connection to execute a custom query (which > > > runs fine in psql) like this: > > > >>> result = ActiveRecord::Base.connection.execute("COPY (select * > > >>> from users) TO STDOUT WITH CSV;") > > > SQL (0.8ms) COPY (select * from users) TO STDOUT WITH CSV; > > > => #<PGresult:0x2589ad8> > > >>> result.nfields > > > => 39 > > >>> result.ntuples > > > => 0 > > > > An instance of PGresult is returned. How can I get data out of it? > > > Load up the rdocs for the postgres gem... it will tell you.... but why > > do it this way? Why not use AR to get your records and fastercsv to > > convert it to CSV? Much more portable... --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
