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
Module Sequel::Postgres::Dataset::BindArgumentMethods
Module Sequel::Postgres::Dataset::PreparedStatementMethods

Constants

DatasetClass = self
APOS = Sequel::Dataset::APOS
DEFAULT_CURSOR_NAME = 'sequel_cursor'.freeze
PREPARED_ARG_PLACEHOLDER = LiteralString.new('$').freeze

Public Instance methods

Execute the given type of statement with the hash of values.

[Source]

     # File lib/sequel/adapters/postgres.rb, line 779
779:         def call(type, bind_vars=OPTS, *values, &block)
780:           ps = to_prepared_statement(type, values)
781:           ps.extend(BindArgumentMethods)
782:           ps.call(bind_vars, &block)
783:         end

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

[Source]

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

Use a cursor for paging.

[Source]

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

Prepare the given type of statement with the given name, and store it in the database to be called later.

[Source]

     # File lib/sequel/adapters/postgres.rb, line 787
787:         def prepare(type, name=nil, *values)
788:           ps = to_prepared_statement(type, values)
789:           ps.extend(PreparedStatementMethods)
790:           if name
791:             ps.prepared_statement_name = name
792:             db.set_prepared_statement(name, ps)
793:           end
794:           ps
795:         end

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

[Source]

     # File lib/sequel/adapters/postgres.rb, line 801
801:         def prepared_arg_placeholder
802:           PREPARED_ARG_PLACEHOLDER
803:         end

Uses a cursor for fetching records, instead of fetching the entire result set at once. 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 683
683:       def use_cursor(opts=OPTS)
684:         clone(:cursor=>{:rows_per_fetch=>1000}.merge(opts))
685:       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 695
695:       def where_current_of(cursor_name=DEFAULT_CURSOR_NAME)
696:         clone(:where=>Sequel.lit(['CURRENT OF '], Sequel.identifier(cursor_name)))
697:       end

[Validate]