Parsing a PostgreSQL result object in a Rails app

Question

I am writing an app that needs to quickly process hundreds of thousands of rows of data, so I've looked into nesting raw SQL in my Ruby code using ActiveRecord::Base.connection.execute, which is working beautifully. However whenever I run it I get the following Object as a result:

#<PG::Result:0x007fe158ab18c8 status=PGRES_TUPLES_OK ntuples=0 nfields=1 cmd_tuples=0>

I've googled around and can't find a way to parse the PG Result into something actually useful. Is there any built-in PG way to do this, or a workaround, or anything really?

Here is the query I'm using:

SELECT row_to_json(row(company_name, ccn_short_title, title))
FROM contents
WHERE contents.company_name = '#{company_name}'
AND contents.title = '#{title}';

Don't use raw SQL unless you absolutely can't avoid it. Writing raw SQL often ties you directly to a specific DBM which means portability and maintenance problems and negates the advantages of using an ORM. — the Tin Man
– the Tin Man, Commented Jun 9, 2015 at 23:38

twonegatives · Accepted Answer · 2015-06-09 20:25:09Z

8

Actually PG::Result responds to many well-known methods from Enumerable module. You can output them all to watch for the desired ones:

query = "SELECT row_to_json(row) from (select * from users) row"
result = ActiveRecord::Base.connection.execute(query)
result.methods - Object.methods
# => returns an array of methods which can be used

For example, you could iterate the results and map them to something more suitable...

result.map do |row|
  JSON.parse(row["row_to_json"])
end
# => returns familiar hashes

Get a desired result hash by its index...

result[0]

And much more.

answered Jun 9, 2015 at 20:25

twonegatives

3,46821 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Parsing a PostgreSQL result object in a Rails app

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related