class Sequel::Postgres::Dataset

Dataset class for PostgreSQL datasets that use the pg, postgres, or postgres-pr driver.

Constants

APOS
DEFAULT_CURSOR_NAME
PREPARED_ARG_PLACEHOLDER

Public Instance Methods

call(type, bind_vars=OPTS, *values, &block) click to toggle source

Execute the given type of statement with the hash of values.

# File lib/sequel/adapters/postgres.rb, line 779
def call(type, bind_vars=OPTS, *values, &block)
  ps = to_prepared_statement(type, values)
  ps.extend(BindArgumentMethods)
  ps.call(bind_vars, &block)
end
fetch_rows(sql) { |h| ... } click to toggle source

Yield all rows returned by executing the given SQL and converting the types.

# File lib/sequel/adapters/postgres.rb, line 653
def fetch_rows(sql)
  return cursor_fetch_rows(sql){|h| yield h} if @opts[:cursor]
  execute(sql){|res| yield_hash_rows(res, fetch_rows_set_cols(res)){|h| yield h}}
end
paged_each(opts=OPTS, &block) click to toggle source

Use a cursor for paging.

# File lib/sequel/adapters/postgres.rb, line 659
def paged_each(opts=OPTS, &block)
  use_cursor(opts).each(&block)
end
prepare(type, name=nil, *values) click to toggle source

Prepare the given type of statement with the given name, and store it in the database to be called later.

# File lib/sequel/adapters/postgres.rb, line 787
def prepare(type, name=nil, *values)
  ps = to_prepared_statement(type, values)
  ps.extend(PreparedStatementMethods)
  if name
    ps.prepared_statement_name = name
    db.set_prepared_statement(name, ps)
  end
  ps
end
prepared_arg_placeholder() click to toggle source

PostgreSQL uses $N for placeholders instead of ?, so use a $ as the placeholder.

# File lib/sequel/adapters/postgres.rb, line 801
def prepared_arg_placeholder
  PREPARED_ARG_PLACEHOLDER
end
use_cursor(opts=OPTS) click to toggle source

Uses a cursor for fetching records, instead of fetching the entire result set at once. Can be used to process large datasets without holding all rows in memory (which is what the underlying drivers may do by default). Options:

:cursor_name

The name assigned to the cursor (default 'sequel_cursor'). Nested cursors require different names.

:hold

Declare the cursor WITH HOLD and don't use transaction around the cursor usage.

:rows_per_fetch

The number of rows per fetch (default 1000). Higher numbers result in fewer queries but greater memory use.

Usage:

DB[:huge_table].use_cursor.each{|row| p row}
DB[:huge_table].use_cursor(:rows_per_fetch=>10000).each{|row| p row}
DB[:huge_table].use_cursor(:cursor_name=>'my_cursor').each{|row| p row}

This is untested with the prepared statement/bound variable support, and unlikely to work with either.

# File lib/sequel/adapters/postgres.rb, line 683
def use_cursor(opts=OPTS)
  clone(:cursor=>{:rows_per_fetch=>1000}.merge(opts))
end
where_current_of(cursor_name=DEFAULT_CURSOR_NAME) click to toggle source

Replace the WHERE clause with one that uses CURRENT OF with the given cursor name (or the default cursor name). This allows you to update a large dataset by updating individual rows while processing the dataset via a cursor:

DB[:huge_table].use_cursor(:rows_per_fetch=>1).each do |row|
  DB[:huge_table].where_current_of.update(:column=>ruby_method(row))
end
# File lib/sequel/adapters/postgres.rb, line 695
def where_current_of(cursor_name=DEFAULT_CURSOR_NAME)
  clone(:where=>Sequel.lit(['CURRENT OF '], Sequel.identifier(cursor_name)))
end

Private Instance Methods

cursor_fetch_rows(sql) { |h| ... } click to toggle source

Use a cursor to fetch groups of records at a time, yielding them to the block.

# File lib/sequel/adapters/postgres.rb, line 809
def cursor_fetch_rows(sql)
  server_opts = {:server=>@opts[:server] || :read_only}
  cursor = @opts[:cursor]
  hold = cursor[:hold]
  cursor_name = quote_identifier(cursor[:cursor_name] || DEFAULT_CURSOR_NAME)
  rows_per_fetch = cursor[:rows_per_fetch].to_i

  db.send(*(hold ? [:synchronize, server_opts[:server]] : [:transaction, server_opts])) do 
    begin
      execute_ddl("DECLARE #{cursor_name} NO SCROLL CURSOR WITH#{'OUT' unless hold} HOLD FOR #{sql}", server_opts)
      rows_per_fetch = 1000 if rows_per_fetch <= 0
      fetch_sql = "FETCH FORWARD #{rows_per_fetch} FROM #{cursor_name}"
      cols = nil
      # Load columns only in the first fetch, so subsequent fetches are faster
      execute(fetch_sql) do |res|
        cols = fetch_rows_set_cols(res)
        yield_hash_rows(res, cols){|h| yield h}
        return if res.ntuples < rows_per_fetch
      end
      loop do
        execute(fetch_sql) do |res|
          yield_hash_rows(res, cols){|h| yield h}
          return if res.ntuples < rows_per_fetch
        end
      end
    rescue Exception => e
      raise
    ensure
      begin
        execute_ddl("CLOSE #{cursor_name}", server_opts)
      rescue
        raise e if e
        raise
      end
    end
  end
end
fetch_rows_set_cols(res) click to toggle source

Set the @columns based on the result set, and return the array of field numers, type conversion procs, and name symbol arrays.

# File lib/sequel/adapters/postgres.rb, line 849
def fetch_rows_set_cols(res)
  cols = []
  procs = db.conversion_procs
  res.nfields.times do |fieldnum|
    cols << [fieldnum, procs[res.ftype(fieldnum)], output_identifier(res.fname(fieldnum))]
  end
  @columns = cols.map{|c| c.at(2)}
  cols
end
literal_blob_append(sql, v) click to toggle source

Use the driver's escape_bytea

# File lib/sequel/adapters/postgres.rb, line 860
def literal_blob_append(sql, v)
  sql << APOS << db.synchronize(@opts[:server]){|c| c.escape_bytea(v)} << APOS
end
literal_string_append(sql, v) click to toggle source

Use the driver's escape_string

# File lib/sequel/adapters/postgres.rb, line 865
def literal_string_append(sql, v)
  sql << APOS << db.synchronize(@opts[:server]){|c| c.escape_string(v)} << APOS
end
yield_hash_rows(res, cols) { |converted_rec| ... } click to toggle source

For each row in the result set, yield a hash with column name symbol keys and typecasted values.

# File lib/sequel/adapters/postgres.rb, line 871
def yield_hash_rows(res, cols)
  res.ntuples.times do |recnum|
    converted_rec = {}
    cols.each do |fieldnum, type_proc, fieldsym|
      value = res.getvalue(recnum, fieldnum)
      converted_rec[fieldsym] = (value && type_proc) ? type_proc.call(value) : value
    end
    yield converted_rec
  end
end