class Sequel::Postgres::Dataset

  1. lib/sequel/adapters/postgres.rb
Superclass: Dataset

Constants

BindArgumentMethods = prepared_statements_module(:bind, [ArgumentMapper], %w'execute execute_dui')  
PREPARED_ARG_PLACEHOLDER = LiteralString.new('$').freeze  

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

Public Instance methods

bound_variable_modules()
[show source] 
    # File lib/sequel/adapters/postgres.rb
738 def bound_variable_modules
739   [BindArgumentMethods]
740 end
fetch_rows(sql)
[show source] 
    # File lib/sequel/adapters/postgres.rb
649 def fetch_rows(sql)
650   return cursor_fetch_rows(sql){|h| yield h} if @opts[:cursor]
651   execute(sql){|res| yield_hash_rows(res, fetch_rows_set_cols(res)){|h| yield h}}
652 end
paged_each(opts=OPTS, &block)

Use a cursor for paging.

[show source] 
    # File lib/sequel/adapters/postgres.rb
655 def paged_each(opts=OPTS, &block)
656   unless defined?(yield)
657     return enum_for(:paged_each, opts)
658   end
659   use_cursor(opts).each(&block)
660 end
prepared_arg_placeholder()

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

[show source] 
    # File lib/sequel/adapters/postgres.rb
748 def prepared_arg_placeholder
749   PREPARED_ARG_PLACEHOLDER
750 end
prepared_statement_modules()
[show source] 
    # File lib/sequel/adapters/postgres.rb
742 def prepared_statement_modules
743   [PreparedStatementMethods]
744 end
use_cursor(opts=OPTS)

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.

[show source] 
    # File lib/sequel/adapters/postgres.rb
685 def use_cursor(opts=OPTS)
686   clone(:cursor=>{:rows_per_fetch=>1000}.merge!(opts))
687 end
where_current_of(cursor_name='sequel_cursor')

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
[show source] 
    # File lib/sequel/adapters/postgres.rb
697 def where_current_of(cursor_name='sequel_cursor')
698   clone(:where=>Sequel.lit(['CURRENT OF '], Sequel.identifier(cursor_name)))
699 end