Class: Sequel::Postgres::Dataset

Inherits:

Dataset

• Object
• Dataset
• Sequel::Postgres::Dataset

Includes:

DatasetMethods

Defined in:

lib/sequel/adapters/postgres.rb

Defined Under Namespace

Modules: ArgumentMapper

Constant Summary

PREPARED_ARG_PLACEHOLDER =

:nocov:

LiteralString.new('$').freeze

BindArgumentMethods =

prepared_statements_module(:bind, [ArgumentMapper], %w'execute execute_dui')

PreparedStatementMethods =

prepared_statements_module(:prepare, BindArgumentMethods, %w'execute execute_dui')

Constants included from DatasetMethods

Sequel::Postgres::DatasetMethods::LOCK_MODES, Sequel::Postgres::DatasetMethods::NULL

Instance Method Summary

• #fetch_rows(sql) ⇒ Object
• #paged_each(opts = OPTS, &block) ⇒ Object

  Use a cursor for paging.

• #use_cursor(opts = OPTS) ⇒ Object

  Uses a cursor for fetching records, instead of fetching the entire result set at once.

• #where_current_of(cursor_name = 'sequel_cursor') ⇒ Object

  Replace the WHERE clause with one that uses CURRENT OF with the given cursor name (or the default cursor name).

Methods included from DatasetMethods

#analyze, #complex_expression_sql_append, #disable_insert_returning, #empty?, #explain, #for_key_share, #for_no_key_update, #for_portion_of, #for_share, #full_text_search, #insert, #insert_conflict, #insert_ignore, #insert_select, #insert_select_sql, #join_table, #lock, #merge, #merge_delete_when_not_matched_by_source, #merge_do_nothing_when_matched, #merge_do_nothing_when_not_matched, #merge_do_nothing_when_not_matched_by_source, #merge_insert, #merge_update_when_not_matched_by_source, #overriding_system_value, #overriding_user_value, #supports_cte?, #supports_cte_in_subqueries?, #supports_distinct_on?, #supports_group_cube?, #supports_group_rollup?, #supports_grouping_sets?, #supports_insert_conflict?, #supports_insert_select?, #supports_lateral_subqueries?, #supports_merge?, #supports_modifying_joins?, #supports_nowait?, #supports_regexp?, #supports_returning?, #supports_skip_locked?, #supports_timestamp_timezones?, #supports_window_clause?, #supports_window_function_frame_option?, #supports_window_functions?, #truncate, #with_ties

Instance Method Details

#fetch_rows(sql) ⇒ Object

# File 'lib/sequel/adapters/postgres.rb', line 650

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) ⇒ Object

Use a cursor for paging.

# File 'lib/sequel/adapters/postgres.rb', line 656

def paged_each(opts=OPTS, &block)
  unless defined?(yield)
    return enum_for(:paged_each, opts)
  end
  use_cursor(opts).each(&block)
end

#use_cursor(opts = OPTS) ⇒ Object

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. :skip_transaction :: Same as :hold, but :hold takes priority.

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 686

def use_cursor(opts=OPTS)
  clone(:cursor=>{:rows_per_fetch=>1000}.merge!(opts))
end

#where_current_of(cursor_name = 'sequel_cursor') ⇒ Object

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 698

def where_current_of(cursor_name='sequel_cursor')
  clone(:where=>Sequel.lit(['CURRENT OF '], Sequel.identifier(cursor_name)))
end