class Sequel::Postgres::Dataset
Constants
- BindArgumentMethods
- PREPARED_ARG_PLACEHOLDER
- PreparedStatementMethods
Public Instance Methods
# File lib/sequel/adapters/postgres.rb 698 def bound_variable_modules 699 [BindArgumentMethods] 700 end
# File lib/sequel/adapters/postgres.rb 612 def fetch_rows(sql) 613 return cursor_fetch_rows(sql){|h| yield h} if @opts[:cursor] 614 execute(sql){|res| yield_hash_rows(res, fetch_rows_set_cols(res)){|h| yield h}} 615 end
Use a cursor for paging.
# File lib/sequel/adapters/postgres.rb 618 def paged_each(opts=OPTS, &block) 619 unless defined?(yield) 620 return enum_for(:paged_each, opts) 621 end 622 use_cursor(opts).each(&block) 623 end
PostgreSQL uses $N for placeholders instead of ?, so use a $ as the placeholder.
# File lib/sequel/adapters/postgres.rb 708 def prepared_arg_placeholder 709 PREPARED_ARG_PLACEHOLDER 710 end
# File lib/sequel/adapters/postgres.rb 702 def prepared_statement_modules 703 [PreparedStatementMethods] 704 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 647 def use_cursor(opts=OPTS) 648 clone(:cursor=>{:rows_per_fetch=>1000}.merge!(opts)) 649 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 659 def where_current_of(cursor_name='sequel_cursor') 660 clone(:where=>Sequel.lit(['CURRENT OF '], Sequel.identifier(cursor_name))) 661 end
Private Instance Methods
Generate and execute a procedure call.
# File lib/sequel/adapters/postgres.rb 716 def call_procedure(name, args) 717 sql = String.new 718 sql << "CALL " 719 identifier_append(sql, name) 720 sql << "(" 721 expression_list_append(sql, args) 722 sql << ")" 723 with_sql_first(sql) 724 end
Use a cursor to fetch groups of records at a time, yielding them to the block.
# File lib/sequel/adapters/postgres.rb 727 def cursor_fetch_rows(sql) 728 server_opts = {:server=>@opts[:server] || :read_only} 729 cursor = @opts[:cursor] 730 hold = cursor[:hold] 731 cursor_name = quote_identifier(cursor[:cursor_name] || 'sequel_cursor') 732 rows_per_fetch = cursor[:rows_per_fetch].to_i 733 734 db.public_send(*(hold ? [:synchronize, server_opts[:server]] : [:transaction, server_opts])) do 735 begin 736 execute_ddl("DECLARE #{cursor_name} NO SCROLL CURSOR WITH#{'OUT' unless hold} HOLD FOR #{sql}", server_opts) 737 rows_per_fetch = 1000 if rows_per_fetch <= 0 738 fetch_sql = "FETCH FORWARD #{rows_per_fetch} FROM #{cursor_name}" 739 cols = nil 740 # Load columns only in the first fetch, so subsequent fetches are faster 741 execute(fetch_sql) do |res| 742 cols = fetch_rows_set_cols(res) 743 yield_hash_rows(res, cols){|h| yield h} 744 return if res.ntuples < rows_per_fetch 745 end 746 while true 747 execute(fetch_sql) do |res| 748 yield_hash_rows(res, cols){|h| yield h} 749 return if res.ntuples < rows_per_fetch 750 end 751 end 752 rescue Exception => e 753 raise 754 ensure 755 begin 756 execute_ddl("CLOSE #{cursor_name}", server_opts) 757 rescue 758 raise e if e 759 raise 760 end 761 end 762 end 763 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 767 def fetch_rows_set_cols(res) 768 cols = [] 769 procs = db.conversion_procs 770 res.nfields.times do |fieldnum| 771 cols << [procs[res.ftype(fieldnum)], output_identifier(res.fname(fieldnum))] 772 end 773 self.columns = cols.map{|c| c[1]} 774 cols 775 end
Use the driver's escape_bytea
# File lib/sequel/adapters/postgres.rb 778 def literal_blob_append(sql, v) 779 sql << "'" << db.synchronize(@opts[:server]){|c| c.escape_bytea(v)} << "'" 780 end
Use the driver's escape_string
# File lib/sequel/adapters/postgres.rb 783 def literal_string_append(sql, v) 784 sql << "'" << db.synchronize(@opts[:server]){|c| c.escape_string(v)} << "'" 785 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 789 def yield_hash_rows(res, cols) 790 ntuples = res.ntuples 791 recnum = 0 792 while recnum < ntuples 793 fieldnum = 0 794 nfields = cols.length 795 converted_rec = {} 796 while fieldnum < nfields 797 type_proc, fieldsym = cols[fieldnum] 798 value = res.getvalue(recnum, fieldnum) 799 converted_rec[fieldsym] = (value && type_proc) ? type_proc.call(value) : value 800 fieldnum += 1 801 end 802 yield converted_rec 803 recnum += 1 804 end 805 end