ruby, Postgres, print sql result in a nice table

Question

In Postgres if you connect to a database and issued this query in the command line, you will get these results :

dvdrental=# SELECT 
dvdrental-#   nspname AS schemaname,relname,reltuples
dvdrental-# FROM pg_class C
dvdrental-# LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
dvdrental-# WHERE 
dvdrental-#   nspname NOT IN ('pg_catalog', 'information_schema') AND
dvdrental-#   relkind='r' 
dvdrental-# ORDER BY reltuples DESC;
schemaname |    relname    | reltuples 
------------+---------------+-----------
public     | rental        |     16044
public     | payment       |     14596
public     | film_actor    |      5462
public     | inventory     |      4581
public     | film_category |      1000
public     | film          |      1000
public     | address       |       603
public     | city          |       600
public     | customer      |       599
public     | actor         |       200
public     | country       |       109
public     | category      |        16
public     | language      |         6
public     | store         |         2
public     | staff         |         2
(15 rows)

This was in Postgres command line, now I run the above query in ruby code, and I got results, then I converted it to an array of hashes :

[{"schemaname"=>"public", "relname"=>"rental", "reltuples"=>"16044"}, {"schemaname"=>"public", "relname"=>"payment", "reltuples"=>"14596"}, {"schemaname"=>"public", "relname"=>"film_actor", "reltuples"=>"5462"}, {"schemaname"=>"public", "relname"=>"inventory", "reltuples"=>"4581"}, {"schemaname"=>"public", "relname"=>"film_category", "reltuples"=>"1000"}, {"schemaname"=>"public", "relname"=>"film", "reltuples"=>"1000"}, {"schemaname"=>"public", "relname"=>"address", "reltuples"=>"603"}, {"schemaname"=>"public", "relname"=>"city", "reltuples"=>"600"}, {"schemaname"=>"public", "relname"=>"customer", "reltuples"=>"599"}, {"schemaname"=>"public", "relname"=>"actor", "reltuples"=>"200"}, {"schemaname"=>"public", "relname"=>"country", "reltuples"=>"109"}, {"schemaname"=>"public", "relname"=>"category", "reltuples"=>"16"}, {"schemaname"=>"public", "relname"=>"language", "reltuples"=>"6"}, {"schemaname"=>"public", "relname"=>"store", "reltuples"=>"2"}, {"schemaname"=>"public", "relname"=>"staff", "reltuples"=>"2"}]

Is there any nice way on how to print these array of hashes to appear exactley the sameway as it was printed from the command line ? Thanks.

I found this solution : https://stackoverflow.com/questions/16030738/print-table-to-file-from-an-array-of-hashes-in-ruby , but I got : undefined method `join' for nil:NilClass (NoMethodError) — Fatna, Apr 15 '18 at 05:42

iGian · Accepted Answer · 2018-04-15T14:27:54.157

Maybe this can help.

pg_array = [{"schemaname"=>"public", "relname"=>"rental", "reltuples"=>"16044"},
                        {"schemaname"=>"public", "relname"=>"payment", "reltuples"=>"14596"},
                        {"schemaname"=>"public", "relname"=>"film_actor", "reltuples"=>"5462"},
                        {"schemaname"=>"public", "relname"=>"inventory", "reltuples"=>"4581"},
                        {"schemaname"=>"public", "relname"=>"film_category", "reltuples"=>"1000"},
                        {"schemaname"=>"public", "relname"=>"film", "reltuples"=>"1000"},
                        {"schemaname"=>"public", "relname"=>"address", "reltuples"=>"603"},
                        {"schemaname"=>"public", "relname"=>"city", "reltuples"=>"600"},
                        {"schemaname"=>"public", "relname"=>"customer", "reltuples"=>"599"},
                        {"schemaname"=>"public", "relname"=>"actor", "reltuples"=>"200"},
                        {"schemaname"=>"public", "relname"=>"country", "reltuples"=>"109"},
                        {"schemaname"=>"public", "relname"=>"category", "reltuples"=>"16"},
                        {"schemaname"=>"public", "relname"=>"language", "reltuples"=>"6"},
                        {"schemaname"=>"public", "relname"=>"store", "reltuples"=>"2"},
                        {"schemaname"=>"public", "relname"=>"staff", "reltuples"=>"2"}]

# split into headers and data
data = pg_array.map(&:values)
headers = pg_array.map(&:keys).uniq[0]


# finde each column width
col_width = []
headers.each {|header| col_width << header.size }

data.each do |row|
  row.each_with_index do |value, index|
    col_width[index] = value.length if value.length > col_width[index]
  end
end

# give 1 space more
col_width.map! {|e| e+1} 


# print the output
def print_row(row_array, col_width)
  row_array.each_with_index do |val, index|
    print "|"
    print val
    print " "*(col_width[index]-val.length)
  end
  print "|\n"
end

print_row(headers, col_width)
print "-"*10 + "\n" # change to print the proper separator
data.each {|row| print_row(row, col_width)}
puts "(#{data.size} rows)"

it looks like there is a problem with the method print_row, now it print only (15 rows) |staff |2 | BUT if I add for example puts "k" at the beginning of print_row it works but replace the firsr character of the first column with "k" — Fatna, Apr 15 '18 at 13:21
"k" "k"-------e |relname |reltuples | "k"blic |rental |16044 | — Fatna, Apr 15 '18 at 13:23
@Fatna, I got it: just replaced `\r` with `\n`. I was testing on SublimeText not in terminal. Updated the code in the post. — iGian, Apr 15 '18 at 14:29
Take a look [here](https://stackoverflow.com/questions/1761051/difference-between-n-and-r) — iGian, Apr 15 '18 at 17:15

ruby, Postgres, print sql result in a nice table

1 Answers1