Class Sequel::Postgres::Dataset
In: lib/sequel/adapters/postgres.rb
Parent: Sequel::Dataset

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

Methods

Included Modules

Sequel::Postgres::DatasetMethods

Classes and Modules

Module Sequel::Postgres::Dataset::ArgumentMapper

Constants

DatasetClass = self
APOS = Sequel::Dataset::APOS
DEFAULT_CURSOR_NAME = 'sequel_cursor'.freeze
PREPARED_ARG_PLACEHOLDER = LiteralString.new('$').freeze
BindArgumentMethods = prepared_statements_module(:bind, [ArgumentMapper, ::Sequel::Postgres::DatasetMethods::PreparedStatementMethods], %w'execute execute_dui')
PreparedStatementMethods = prepared_statements_module(:prepare, BindArgumentMethods, %w'execute execute_dui') do # Raise a more obvious error if you attempt to call a unnamed prepared statement. def call(*)

Public Instance methods

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

[Source]

     # File lib/sequel/adapters/postgres.rb, line 675
675:       def fetch_rows(sql)
676:         return cursor_fetch_rows(sql){|h| yield h} if @opts[:cursor]
677:         execute(sql){|res| yield_hash_rows(res, fetch_rows_set_cols(res)){|h| yield h}}
678:       end

Use a cursor for paging.

[Source]

     # File lib/sequel/adapters/postgres.rb, line 681
681:       def paged_each(opts=OPTS, &block)
682:         use_cursor(opts).each(&block)
683:       end

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.

[Source]

     # File lib/sequel/adapters/postgres.rb, line 707
707:       def use_cursor(opts=OPTS)
708:         clone(:cursor=>{:rows_per_fetch=>1000}.merge!(opts))
709:       end

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

[Source]

     # File lib/sequel/adapters/postgres.rb, line 719
719:       def where_current_of(cursor_name=DEFAULT_CURSOR_NAME)
720:         clone(:where=>Sequel.lit(['CURRENT OF '], Sequel.identifier(cursor_name)))
721:       end

[Validate]