class Sequel::Postgres::Dataset

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

Constants

APOS
BindArgumentMethods
DEFAULT_CURSOR_NAME
PREPARED_ARG_PLACEHOLDER
PreparedStatementMethods

Public Instance Methods

bound_variable_modules() click to toggle source
# File lib/sequel/adapters/postgres.rb, line 768
def bound_variable_modules
  [BindArgumentMethods]
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 679
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 685
def paged_each(opts=OPTS, &block)
  use_cursor(opts).each(&block)
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 778
def prepared_arg_placeholder
  PREPARED_ARG_PLACEHOLDER
end
prepared_statement_modules() click to toggle source
# File lib/sequel/adapters/postgres.rb, line 772
def prepared_statement_modules
  [PreparedStatementMethods]
end
use_cursor(opts=OPTS) click to toggle source

Uses a cursor for fetching records, instead of fetching the entire result set at once. Note this uses a transaction around the cursor usage by default and can be changed using `hold: true` as described below. Cursors 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 711
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 723
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 786
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 826
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
  self.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 837
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 842
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 848
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