Talking Directly to a PostgreSQL Database

Table of contents:

Problem

You want to send SQL queries and commands directly to a PostgreSQL database.

Solution

As with the MySQL recipe preceding this one, ask: do you really need to do this? The generic DBI library usually works just fine. As before, the main exception is when you need to make low-level SQL calls from within a Rails application.

There are two APIs for communicating with a PostgreSQL database, and both are available as gems. The postgres gem provides a Ruby binding to the C client library, and the postgres-pr gem provides a pure Ruby interface.

Heres a Postgres-specific version of the method with_db, defined in the chapter intro. It returns a PGconn object, which you can use to run queries or get server information. This code assumes you e accessing the database through TCP/IP on port 5432 of your local machine.

	require 
ubygems
	require postgres

	def with_db
	 db = PGconn.connect(localhost, 5432, \, \, cookbook,
	 cookbook_user, password)
	 begin
	 yield db
	 ensure
	 db.close
	 end
	end

The PGconn#exec method runs any SQL statement, whether its a SELECT query or something else. When it runs a query, the return value is a result-set object (a PGresult); otherwise, its nil. Here it is running some SQL commands:

	with_db do |db|
	 begin
	 db.exec(drop table secrets)
	 rescue PGError
	 # Unlike MySQL, Postgres does not have a "drop table unless exists"
	 # command. We can simulate it by issuing a "drop table" command and
	 # ignoring any error due to the table not existing in the first place.
	 # This is essentialy what MySQLs "drop table unless exists" does.
	 end

	 db.exec(create table secrets( id SERIAL PRIMARY KEY,
	 secret TEXT ))
	 db.exec(%{insert into secrets(secret) values
	 (Oh, Postgres, you\
e the only one who really understands me.)})
	end

Heres a query:

	with_db do |db|
	 res = db.query(select * from secrets)
	 res.each { |row| puts "#{row[0]}: #{row[1]}" }
	end
	# 1: Oh, Postgres, you
e the only one who really understands me.

Discussion

Note the slight differences between the Postgres implementation of SQL and the MySQL implementation. The "drop table if exists" syntax is MySQL-specific. Postgres names the data types differently, and expects string values to be single-quoted.

Like the database connection itself, the result set you get from exec wants to be closed when you e done with it. As we did with query in the MySQL binding, we can alter exec to take an optional code block and do the cleanup for us:

	class PGconn
	 alias :exec_no_block :exec
	 def exec(sql)
	 res = exec_no_block(sql)
	 return res unless block_given?
	 begin
	 yield res
	 ensure
	 res.clear if res
	 end
	 end
	end

Now we can write more concise query code, and not have to worry about freeing the result set:

	with_db do |db|
	 db.exec(select * from secrets) do |res|
	 res.each { |row| puts "#{row[0]}: #{row[1]}" }
	 end
	end
	# 1: Oh, Postgres, you
e the only one who really understands me.

The method PGresult#each yields you the rows of a result set as arrays, and PGresult#num_tuples gives you the number of rows matched by a query. The Postgres database binding has no equivalent of the MySQL bindings each_hash, but you can write one pretty easily:

	class PGresult
	 def each_hash
	 f = fields
	 each do |array|
	 hash = {}
	 fields.each_with_index do |field, i|
	 hash[field] = array[i]
	 end
	 yield hash
	 end
	 end
	end

Here it is in action:

	with_db do |db|
	 db.exec("select * from secrets") do |res|
	 puts "#{res.num_tuples} row(s) matched:"
	 res.each_hash do |hash|
	 hash.each { |k,v| puts " #{k} = #{v}" }
	 end
	 end
	end
	# 1 row(s) matched:
	# id = 1
	# secret = Oh, Postgres, you
e the only one who really understands me.

Problem

Solution

Discussion

See Also