class Sequel::Postgres::Dataset

Constants

BindArgumentMethods
PREPARED_ARG_PLACEHOLDER

:nocov:

PreparedStatementMethods

Public Instance Methods

bound_variable_modules() click to toggle source
    # File lib/sequel/adapters/postgres.rb
737 def bound_variable_modules
738   [BindArgumentMethods]
739 end
fetch_rows(sql) { |h| ... } click to toggle 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) { || ... } click to toggle source

Use a cursor for paging.

    # 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() click to toggle source

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

    # File lib/sequel/adapters/postgres.rb
747 def prepared_arg_placeholder
748   PREPARED_ARG_PLACEHOLDER
749 end
prepared_statement_modules() click to toggle source
    # File lib/sequel/adapters/postgres.rb
741 def prepared_statement_modules
742   [PreparedStatementMethods]
743 end
use_cursor(opts=OPTS) click to toggle source

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.

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

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
696 def where_current_of(cursor_name='sequel_cursor')
697   clone(:where=>Sequel.lit(['CURRENT OF '], Sequel.identifier(cursor_name)))
698 end

Private Instance Methods

call_procedure(name, args) click to toggle source

Generate and execute a procedure call.

    # File lib/sequel/adapters/postgres.rb
755 def call_procedure(name, args)
756   sql = String.new
757   sql << "CALL "
758   identifier_append(sql, name)
759   sql << "("
760   expression_list_append(sql, args)
761   sql << ")"
762   with_sql_first(sql)
763 end
cursor_fetch_rows(sql) { |h| ... } click to toggle source

Use a cursor to fetch groups of records at a time, yielding them to the block.

    # File lib/sequel/adapters/postgres.rb
766 def cursor_fetch_rows(sql)
767   server_opts = {:server=>@opts[:server] || :read_only}
768   cursor = @opts[:cursor]
769   hold = cursor[:hold]
770   cursor_name = quote_identifier(cursor[:cursor_name] || 'sequel_cursor')
771   rows_per_fetch = cursor[:rows_per_fetch].to_i
772 
773   db.public_send(*(hold ? [:synchronize, server_opts[:server]] : [:transaction, server_opts])) do 
774     begin
775       execute_ddl("DECLARE #{cursor_name} NO SCROLL CURSOR WITH#{'OUT' unless hold} HOLD FOR #{sql}", server_opts)
776       rows_per_fetch = 1000 if rows_per_fetch <= 0
777       fetch_sql = "FETCH FORWARD #{rows_per_fetch} FROM #{cursor_name}"
778       cols = nil
779       # Load columns only in the first fetch, so subsequent fetches are faster
780       execute(fetch_sql) do |res|
781         cols = fetch_rows_set_cols(res)
782         yield_hash_rows(res, cols){|h| yield h}
783         return if res.ntuples < rows_per_fetch
784       end
785       while true
786         execute(fetch_sql) do |res|
787           yield_hash_rows(res, cols){|h| yield h}
788           return if res.ntuples < rows_per_fetch
789         end
790       end
791     rescue Exception => e
792       raise
793     ensure
794       begin
795         execute_ddl("CLOSE #{cursor_name}", server_opts)
796       rescue
797         raise e if e
798         raise
799       end
800     end
801   end
802 end
fetch_rows_set_cols(res) click to toggle source

Set the columns based on the result set, and return the array of field numers, type conversion procs, and name symbol arrays.

    # File lib/sequel/adapters/postgres.rb
806 def fetch_rows_set_cols(res)
807   cols = []
808   procs = db.conversion_procs
809   res.nfields.times do |fieldnum|
810     cols << [procs[res.ftype(fieldnum)], output_identifier(res.fname(fieldnum))]
811   end
812   self.columns = cols.map{|c| c[1]}
813   cols
814 end
literal_blob_append(sql, v) click to toggle source

Use the driver's escape_bytea

    # File lib/sequel/adapters/postgres.rb
817 def literal_blob_append(sql, v)
818   sql << "'" << db.synchronize(@opts[:server]){|c| c.escape_bytea(v)} << "'"
819 end
literal_string_append(sql, v) click to toggle source

Use the driver's escape_string

    # File lib/sequel/adapters/postgres.rb
822 def literal_string_append(sql, v)
823   sql << "'" << db.synchronize(@opts[:server]){|c| c.escape_string(v)} << "'"
824 end
yield_hash_rows(res, cols) { |converted_rec| ... } click to toggle source

For each row in the result set, yield a hash with column name symbol keys and typecasted values.

    # File lib/sequel/adapters/postgres.rb
828 def yield_hash_rows(res, cols)
829   ntuples = res.ntuples
830   recnum = 0
831   while recnum < ntuples
832     fieldnum = 0
833     nfields = cols.length
834     converted_rec = {}
835     while fieldnum < nfields
836       type_proc, fieldsym = cols[fieldnum]
837       value = res.getvalue(recnum, fieldnum)
838       converted_rec[fieldsym] = (value && type_proc) ? type_proc.call(value) : value
839       fieldnum += 1 
840     end
841     yield converted_rec
842     recnum += 1
843   end
844 end