class Sequel::Postgres::Dataset
Dataset class for PostgreSQL datasets that use the pg, postgres, or postgres-pr driver.
Constants
- APOS
- BindArgumentMethods
- DEFAULT_CURSOR_NAME
- PREPARED_ARG_PLACEHOLDER
- PreparedStatementMethods
Public Instance Methods
# File lib/sequel/adapters/postgres.rb, line 768 def bound_variable_modules [BindArgumentMethods] end
Yield all rows returned by executing the given SQL and converting the types.
# File lib/sequel/adapters/postgres.rb, line 679 def fetch_rows(sql) return cursor_fetch_rows(sql){|h| yield h} if @opts[:cursor] execute(sql){|res| yield_hash_rows(res, fetch_rows_set_cols(res)){|h| yield h}} end
Use a cursor for paging.
# File lib/sequel/adapters/postgres.rb, line 685 def paged_each(opts=OPTS, &block) use_cursor(opts).each(&block) end
PostgreSQL uses $N for placeholders instead of ?, so use a $ as the placeholder.
# File lib/sequel/adapters/postgres.rb, line 778 def prepared_arg_placeholder PREPARED_ARG_PLACEHOLDER end
# File lib/sequel/adapters/postgres.rb, line 772 def prepared_statement_modules [PreparedStatementMethods] 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.
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, line 711 def use_cursor(opts=OPTS) clone(:cursor=>{:rows_per_fetch=>1000}.merge!(opts)) 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
# File lib/sequel/adapters/postgres.rb, line 723 def where_current_of(cursor_name=DEFAULT_CURSOR_NAME) clone(:where=>Sequel.lit(['CURRENT OF '], Sequel.identifier(cursor_name))) end
Private Instance Methods
Use a cursor to fetch groups of records at a time, yielding them to the block.
# File lib/sequel/adapters/postgres.rb, line 786 def cursor_fetch_rows(sql) server_opts = {:server=>@opts[:server] || :read_only} cursor = @opts[:cursor] hold = cursor[:hold] cursor_name = quote_identifier(cursor[:cursor_name] || DEFAULT_CURSOR_NAME) rows_per_fetch = cursor[:rows_per_fetch].to_i db.send(*(hold ? [:synchronize, server_opts[:server]] : [:transaction, server_opts])) do begin execute_ddl("DECLARE #{cursor_name} NO SCROLL CURSOR WITH#{'OUT' unless hold} HOLD FOR #{sql}", server_opts) rows_per_fetch = 1000 if rows_per_fetch <= 0 fetch_sql = "FETCH FORWARD #{rows_per_fetch} FROM #{cursor_name}" cols = nil # Load columns only in the first fetch, so subsequent fetches are faster execute(fetch_sql) do |res| cols = fetch_rows_set_cols(res) yield_hash_rows(res, cols){|h| yield h} return if res.ntuples < rows_per_fetch end loop do execute(fetch_sql) do |res| yield_hash_rows(res, cols){|h| yield h} return if res.ntuples < rows_per_fetch end end rescue Exception => e raise ensure begin execute_ddl("CLOSE #{cursor_name}", server_opts) rescue raise e if e raise end end end end
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, line 826 def fetch_rows_set_cols(res) cols = [] procs = db.conversion_procs res.nfields.times do |fieldnum| cols << [fieldnum, procs[res.ftype(fieldnum)], output_identifier(res.fname(fieldnum))] end self.columns = cols.map{|c| c.at(2)} cols end
Use the driver's escape_bytea
# File lib/sequel/adapters/postgres.rb, line 837 def literal_blob_append(sql, v) sql << APOS << db.synchronize(@opts[:server]){|c| c.escape_bytea(v)} << APOS end
Use the driver's escape_string
# File lib/sequel/adapters/postgres.rb, line 842 def literal_string_append(sql, v) sql << APOS << db.synchronize(@opts[:server]){|c| c.escape_string(v)} << APOS end
For each row in the result set, yield a hash with column name symbol keys and typecasted values.
# File lib/sequel/adapters/postgres.rb, line 848 def yield_hash_rows(res, cols) res.ntuples.times do |recnum| converted_rec = {} cols.each do |fieldnum, type_proc, fieldsym| value = res.getvalue(recnum, fieldnum) converted_rec[fieldsym] = (value && type_proc) ? type_proc.call(value) : value end yield converted_rec end end