display postgres sql result using pg gem

Question

How do I display the result set of a postgres query using the pg gem only ? I want it to be in tabular form like it is in pgAdmin GUI tool. The code below does not help. The documentation is not clear, so I am unable to figure another way out. Please help !

require 'pg'
conn = PGconn.connect("db.corp.com", 5432, '', '', "schema", "user", "pass")
sql = 'select * from tbl limit 2'
res  = conn.exec(sql)

res.each do |row|
    row.each do |column|    
    end
end

gem list - pg (0.9.0.pre156 x86-mswin32)

ruby - 1.8.7

I think you have really unrealistic expectations about what the PG gem does. Its simply a client to query a postgres server. If you want to have pretty GUI output you actually need to build it yourself or use some other gem. — max
– max, Commented Jun 24, 2015 at 6:58
@maxcal - why do they have such a primitive gem ? can i use active record for this instead ? — stack1
– stack1, Commented Jun 24, 2015 at 21:42
PG is a low level component, kind of like the engine in a car. You don't expect the engine to have comfy seats or a GPS. Its the same with good software - many different parts each working together. Its the same with ActiveRecord - its a bit higher level since you use models instead of RAW SQL but its not the shortcut you are looking for. — max
– max, Commented Jun 25, 2015 at 1:18

stack1 · Accepted Answer · 2015-06-24 22:41:30Z

4

Steps - 1. Get list of column names in result set (type PGResult). 2. Iterate each row(hash) of result set. 3. For each row (hash key)/columns found in step 1, find the column values (hash value).

Then print results as csv. I dont think this is efficient, but it gets the job done.

require 'pg'
conn = PGconn.connect("db.corp.com", 5432, '', '', "schema", "user", "pass")
sql = 'select * from tbl limit 2'
res  = conn.exec(sql)

rows_count = res.num_tuples
column_names = res.fields
col_header = column_names.join(', ')

puts col_header

for i in 0..rows_count-1
    row_hash = res[i]
    row_arr = []
    column_names.each do |col|
        row_arr << row_hash[col]
    end
    row = row_arr.join(', ')
    puts row
end

answered Jun 24, 2015 at 22:41

stack1

1,0322 gold badges13 silver badges28 bronze badges

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

Collectives™ on Stack Overflow

display postgres sql result using pg gem

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