class Sequel::Postgres::Dataset

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


BindArgumentMethods = prepared_statements_module(:bind, [ArgumentMapper], %w'execute execute_dui')  
PreparedStatementMethods = prepared_statements_module(:prepare, BindArgumentMethods, %w'execute execute_dui')  

Public Instance methods

[show source]
    # File lib/sequel/adapters/postgres.rb
698 def bound_variable_modules
699   [BindArgumentMethods]
700 end
[show source]
    # File lib/sequel/adapters/postgres.rb
612 def fetch_rows(sql)
613   return cursor_fetch_rows(sql){|h| yield h} if @opts[:cursor]
614   execute(sql){|res| yield_hash_rows(res, fetch_rows_set_cols(res)){|h| yield h}}
615 end
paged_each(opts=OPTS, &block)

Use a cursor for paging.

[show source]
    # File lib/sequel/adapters/postgres.rb
618 def paged_each(opts=OPTS, &block)
619   unless block_given?
620     return enum_for(:paged_each, opts)
621   end
622   use_cursor(opts).each(&block)
623 end

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

[show source]
    # File lib/sequel/adapters/postgres.rb
708 def prepared_arg_placeholder
710 end
[show source]
    # File lib/sequel/adapters/postgres.rb
702 def prepared_statement_modules
703   [PreparedStatementMethods]
704 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:


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


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


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


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
647 def use_cursor(opts=OPTS)
648   clone(:cursor=>{:rows_per_fetch=>1000}.merge!(opts))
649 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))
[show source]
    # File lib/sequel/adapters/postgres.rb
659 def where_current_of(cursor_name='sequel_cursor')
660   clone(:where=>Sequel.lit(['CURRENT OF '], Sequel.identifier(cursor_name)))
661 end