Ruby PostgreSQL tutorials [closed]

Question

I am trying to write a ruby script that interacts with a PostgreSQL database. I am trying to piece together how to do this from the documentation, but a nice tutorial or sample code would work wonders to decrease the amount of time to get this working. If anyone has a link, some tips or has some code they could share I would be most grateful.

Edit, made this note more clear:

Note: this isn't to do with rails ActiveRecord, I am writing a Ruby script that will be involved in a program that is completely independent from Rails.

Answer 1

Please be more specific about what postgresql library you're using.

I'm going to assume the 'pg' gem, apart from ActiveRecord.

The project source has an html file that might be helpful. Go to https://bitbucket.org/ged/ruby-pg/src/b477174160c8/doc/postgres.html Then click "raw" at the upper right side of the html. Open the file in your web browser.

This sample code helps you connect (copied from the html file):

require "postgres"
conn = PGconn.connect("localhost", 5432, "", "", "test1")
# or: conn = PGconn.open('dbname=test1')
res = conn.exec("select * from a;")

The res object is a PGResult. Scroll down to that section in the html to see what methods you can call.

This link has a PGResult example: http://rubydoc.info/gems/pg/0.10.0/PGresult

Excerpt:

require 'pg'
conn = PGconn.open(:dbname => 'test')
res  = conn.exec('SELECT 1 AS a, 2 AS b, NULL AS c')
res.getvalue(0,0) # '1'
res[0]['b']       # '2'
res[0]['c']       # nil

Answer 2

I confirm, "postgres" package is outdated, you need "pg".

It tooks me lot of time just to get a basic select * from films working with ruby and postgres. As I am kind, here is my code:

postgres preparation (database=megatest user=roger pass=123456 table=films)

$ su postgres 
psql 
CREATE USER roger WITH PASSWORD '123456';

GRANT ALL PRIVILEGES ON DATABASE megatest to roger;

megatest=# GRANT SELECT ON films TO PUBLIC;

PG package preparation

sudo gem install pg

Ruby Code

require 'pg'

conn=PGconn.connect( :hostaddr=>"127.0.0.1", :port=>5432, :dbname=>"megatest", :user=>"roger", :password=>'123456')
# or for a non IP address :host => 'my.host.name.com' instead of hostaddr

# run the query
res = conn.exec("SELECT * FROM films")

# Ran only once in order to get field Name
fieldArray=res.fields()
fieldArray.each do |elem|
    print "elem="+elem+"\n"
end

# print data from the query
res.each{ |row|
    puts "Code="+row["code"]  +" title="+row["title"] +" did="+row["did"] +" date_prod="+row["date_prod"] +" kind="+row["kind"] +" len="+row["len"]
}

Results

root@eblain-VirtualBox:/home/eblain/ruby# ruby postgresTest.rb
Code=UA502 title=Bananas did=105 date_prod=1971-07-13 kind=Comedy len=01:22:00
Code=UA503 title=Cowboy did=105 date_prod=1979-07-13 kind=Horror len=01:32:00
Code=UA544 title=YoBro did=105 date_prod=1981-07-13 kind=Action len=01:42:00