CODEWITHSUNDEEP
rubypostgresqlpg
stack1
stack1

Reputation: 1022

display postgres sql result using pg gem

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

Upvotes: 1

Views: 1843

Answers (1)

stack1
stack1

Reputation: 1022

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

Upvotes: 4

Related Questions