class Sequel::Postgres::Dataset
Constants
- BindArgumentMethods
- PREPARED_ARG_PLACEHOLDER
- PreparedStatementMethods
Public Instance Methods
# File lib/sequel/adapters/postgres.rb 678 def bound_variable_modules 679 [BindArgumentMethods] 680 end
# File lib/sequel/adapters/postgres.rb 590 def fetch_rows(sql) 591 return cursor_fetch_rows(sql){|h| yield h} if @opts[:cursor] 592 execute(sql){|res| yield_hash_rows(res, fetch_rows_set_cols(res)){|h| yield h}} 593 end
Use a cursor for paging.
# File lib/sequel/adapters/postgres.rb 596 def paged_each(opts=OPTS, &block) 597 use_cursor(opts).each(&block) 598 end
PostgreSQL uses $N for placeholders instead of ?, so use a $ as the placeholder.
# File lib/sequel/adapters/postgres.rb 688 def prepared_arg_placeholder 689 PREPARED_ARG_PLACEHOLDER 690 end
# File lib/sequel/adapters/postgres.rb 682 def prepared_statement_modules 683 [PreparedStatementMethods] 684 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 622 def use_cursor(opts=OPTS) 623 clone(:cursor=>{:rows_per_fetch=>1000}.merge!(opts)) 624 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 634 def where_current_of(cursor_name='sequel_cursor') 635 clone(:where=>Sequel.lit(['CURRENT OF '], Sequel.identifier(cursor_name))) 636 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 696 def cursor_fetch_rows(sql) 697 server_opts = {:server=>@opts[:server] || :read_only} 698 cursor = @opts[:cursor] 699 hold = cursor[:hold] 700 cursor_name = quote_identifier(cursor[:cursor_name] || 'sequel_cursor') 701 rows_per_fetch = cursor[:rows_per_fetch].to_i 702 703 db.public_send(*(hold ? [:synchronize, server_opts[:server]] : [:transaction, server_opts])) do 704 begin 705 execute_ddl("DECLARE #{cursor_name} NO SCROLL CURSOR WITH#{'OUT' unless hold} HOLD FOR #{sql}", server_opts) 706 rows_per_fetch = 1000 if rows_per_fetch <= 0 707 fetch_sql = "FETCH FORWARD #{rows_per_fetch} FROM #{cursor_name}" 708 cols = nil 709 # Load columns only in the first fetch, so subsequent fetches are faster 710 execute(fetch_sql) do |res| 711 cols = fetch_rows_set_cols(res) 712 yield_hash_rows(res, cols){|h| yield h} 713 return if res.ntuples < rows_per_fetch 714 end 715 while true 716 execute(fetch_sql) do |res| 717 yield_hash_rows(res, cols){|h| yield h} 718 return if res.ntuples < rows_per_fetch 719 end 720 end 721 rescue Exception => e 722 raise 723 ensure 724 begin 725 execute_ddl("CLOSE #{cursor_name}", server_opts) 726 rescue 727 raise e if e 728 raise 729 end 730 end 731 end 732 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 736 def fetch_rows_set_cols(res) 737 cols = [] 738 procs = db.conversion_procs 739 res.nfields.times do |fieldnum| 740 cols << [fieldnum, procs[res.ftype(fieldnum)], output_identifier(res.fname(fieldnum))] 741 end 742 self.columns = cols.map{|c| c[2]} 743 cols 744 end
Use the driver's escape_bytea
# File lib/sequel/adapters/postgres.rb 747 def literal_blob_append(sql, v) 748 sql << "'" << db.synchronize(@opts[:server]){|c| c.escape_bytea(v)} << "'" 749 end
Use the driver's escape_string
# File lib/sequel/adapters/postgres.rb 752 def literal_string_append(sql, v) 753 sql << "'" << db.synchronize(@opts[:server]){|c| c.escape_string(v)} << "'" 754 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 758 def yield_hash_rows(res, cols) 759 res.ntuples.times do |recnum| 760 converted_rec = {} 761 cols.each do |fieldnum, type_proc, fieldsym| 762 value = res.getvalue(recnum, fieldnum) 763 converted_rec[fieldsym] = (value && type_proc) ? type_proc.call(value) : value 764 end 765 yield converted_rec 766 end 767 end