class Sequel::Postgres::Dataset

Constants

BindArgumentMethods
PREPARED_ARG_PLACEHOLDER: simplecov:enable
PreparedStatementMethods

Public Instance Methods

Source

    # File lib/sequel/adapters/postgres.rb
742 def bound_variable_modules
743   [BindArgumentMethods]
744 end

fetch_rows (sql) { |h| ... }

Source

    # File lib/sequel/adapters/postgres.rb
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

paged_each (opts=OPTS) { || ... }

Source

    # File lib/sequel/adapters/postgres.rb
659 def paged_each(opts=OPTS, &block)
660   unless defined?(yield)
661     return enum_for(:paged_each, opts)
662   end
663   use_cursor(opts).each(&block)
664 end

Use a cursor for paging.

prepared_arg_placeholder ()

Source

    # File lib/sequel/adapters/postgres.rb
752 def prepared_arg_placeholder
753   PREPARED_ARG_PLACEHOLDER
754 end

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

prepared_statement_modules ()

Source

    # File lib/sequel/adapters/postgres.rb
746 def prepared_statement_modules
747   [PreparedStatementMethods]
748 end

use_cursor (opts=OPTS)

Source

    # File lib/sequel/adapters/postgres.rb
689 def use_cursor(opts=OPTS)
690   clone(:cursor=>{:rows_per_fetch=>1000}.merge!(opts))
691 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:

: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.

where_current_of (cursor_name='sequel_cursor')

Source

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

Private Instance Methods

call_procedure (name, args)

Source

    # File lib/sequel/adapters/postgres.rb
760 def call_procedure(name, args)
761   sql = String.new
762   sql << "CALL "
763   identifier_append(sql, name)
764   sql << "("
765   expression_list_append(sql, args)
766   sql << ")"
767   with_sql_first(sql)
768 end

Generate and execute a procedure call.

cursor_fetch_rows (sql) { |h| ... }

Source

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

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

fetch_rows_set_cols (res)

Source

    # File lib/sequel/adapters/postgres.rb
811 def fetch_rows_set_cols(res)
812   cols = []
813   procs = db.conversion_procs
814   res.nfields.times do |fieldnum|
815     cols << [procs[res.ftype(fieldnum)], output_identifier(res.fname(fieldnum))]
816   end
817   self.columns = cols.map{|c| c[1]}
818   cols
819 end

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

literal_blob_append (sql, v)

Source

    # File lib/sequel/adapters/postgres.rb
822 def literal_blob_append(sql, v)
823   sql << "'" << db.synchronize(@opts[:server]){|c| c.escape_bytea(v)} << "'"
824 end

Use the driver’s escape_bytea

literal_string_append (sql, v)

Source

    # File lib/sequel/adapters/postgres.rb
827 def literal_string_append(sql, v)
828   sql << "'" << db.synchronize(@opts[:server]){|c| c.escape_string(v)} << "'"
829 end

Use the driver’s escape_string

yield_hash_rows (res, cols) { |converted_rec| ... }

Source

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

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