Talking Directly to a PostgreSQL Database

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.

See Also

Related

Категории