Dataset class for PostgreSQL datasets that use the pg, postgres, or postgres-pr driver.
Execute the given type of statement with the hash of values.
# File lib/sequel/adapters/postgres.rb, line 706 def call(type, bind_vars={}, *values, &block) ps = to_prepared_statement(type, values) ps.extend(BindArgumentMethods) ps.call(bind_vars, &block) end
Yield all rows returned by executing the given SQL and converting the types.
# File lib/sequel/adapters/postgres.rb, line 597 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
Prepare the given type of statement with the given name, and store it in the database to be called later.
# File lib/sequel/adapters/postgres.rb, line 714 def prepare(type, name=nil, *values) ps = to_prepared_statement(type, values) ps.extend(PreparedStatementMethods) if name ps.prepared_statement_name = name db.set_prepared_statement(name, ps) end ps end
PostgreSQL uses $N for placeholders instead of ?, so use a $ as the placeholder.
# File lib/sequel/adapters/postgres.rb, line 728 def prepared_arg_placeholder PREPARED_ARG_PLACEHOLDER end
Uses a cursor for fetching records, instead of fetching the entire result set at once. Can be used to process large datasets without holding all rows in memory (which is what the underlying drivers do by default). Options:
: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}
This is untested with the prepared statement/bound variable support, and unlikely to work with either.
# File lib/sequel/adapters/postgres.rb, line 617 def use_cursor(opts={}) clone(:cursor=>{:rows_per_fetch=>1000}.merge(opts)) end
Use a cursor to fetch groups of records at a time, yielding them to the block.
# File lib/sequel/adapters/postgres.rb, line 736 def cursor_fetch_rows(sql) server_opts = {:server=>@opts[:server] || :read_only} db.transaction(server_opts) do begin execute_ddl("DECLARE sequel_cursor NO SCROLL CURSOR WITHOUT HOLD FOR #{sql}", server_opts) rows_per_fetch = @opts[:cursor][:rows_per_fetch].to_i rows_per_fetch = 1000 if rows_per_fetch <= 0 fetch_sql = "FETCH FORWARD #{rows_per_fetch} FROM sequel_cursor" 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 ensure execute_ddl("CLOSE sequel_cursor", server_opts) 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 765 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 @columns = cols.map{|c| c.at(2)} cols end
Use the driver's escape_bytea
# File lib/sequel/adapters/postgres.rb, line 776 def literal_blob_append(sql, v) sql << APOS << db.synchronize{|c| c.escape_bytea(v)} << APOS end
Use the driver's escape_string
# File lib/sequel/adapters/postgres.rb, line 781 def literal_string_append(sql, v) sql << APOS << db.synchronize{|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 787 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