class SqlPostgres::Cursor
This class creates and manages a cursor. Example:
** example: cursor
Transaction.new(connection) do select = Select.new(connection) select.select('i') select.from('foo') Cursor.new('my_cursor', select, {}, connection) do |cursor| while !(rows = cursor.fetch).empty? for row in rows p row # {"i"=>0} # {"i"=>1} # {"i"=>2} # {"i"=>3} # {"i"=>4} end end end end
**
Fetching a single row at a time, the default for fetch, is slow. Usually you will want to speed things up by calling, for example, fetch(1000).
Public Class Methods
new(name, select, opts = {}, connection = Connection.default) { |cursor| ... }
click to toggle source
Create a cursor. If a block is given, yield the cursor to the block, closing the cursor when the block returns, and returning results of the block.
If no connection is given, then the default connection is used.
- name
-
The cursor's name
- select
-
a
Select
statement - opts
-
Options hash. Keys are:
:scroll=>(boolean) If true, create a SCROLL cursor. If false, create a NO SCROLL cursor. If not specified, the default is to allow scrolling in cases where performance will not suffer. :hold=>(boolean) If true, create a WITH HOLD cursor. If false, create a HOLD cursor. WITHOUT HOLD is the default. A HOLD cursor can be used outside of the transaction that created it.
- connection
-
The database connection
# File lib/sqlpostgres/Cursor.rb, line 54 def initialize(name, select, opts = {}, connection = Connection.default) if block_given? cursor = self.class.new(name, select, opts, connection) result = yield(cursor) cursor.close result else @name = name @select = select @opts = opts @connection = connection declare_cursor end end
Public Instance Methods
close()
click to toggle source
Close the cursor. Once closed, it may not be closed or fetched from again.
# File lib/sqlpostgres/Cursor.rb, line 107 def close statement = "close #{@name}" @connection.exec(statement) end
fetch(direction = 'NEXT')
click to toggle source
Fetch a row or rows from the cursor.
- direction
-
A string specifying which row or rows to fetch. See the postgres documentation for the “DECLARE” statement.
NEXT PRIOR FIRST LAST ABSOLUTE count RELATIVE count count ALL FORWARD FORWARD count FORWARD ALL BACKWARD BACKWARD count BACKWARD ALL
# File lib/sqlpostgres/Cursor.rb, line 89 def fetch(direction = 'NEXT') @select.fetch_by_cursor(@name, direction, @connection) end
move(direction = 'NEXT')
click to toggle source
Seek a cursor. Works exactly the same (and takes the same arguments) as fetch, but returns no rows.
- direction
-
See
fetch
# File lib/sqlpostgres/Cursor.rb, line 99 def move(direction = 'NEXT') statement = "move #{direction} from #{@name}" @connection.exec(statement) end
Private Instance Methods
declare_cursor()
click to toggle source
# File lib/sqlpostgres/Cursor.rb, line 114 def declare_cursor statement = [ 'declare', @name, scroll_declaration, 'cursor', hold_declaration, 'for', @select.statement ].compact.join(' ') @connection.exec(statement) end
hold_declaration()
click to toggle source
# File lib/sqlpostgres/Cursor.rb, line 138 def hold_declaration value = @opts.fetch(:hold, :default) if value == :default nil elsif value "WITH HOLD" else "WITHOUT HOLD" end end
scroll_declaration()
click to toggle source
# File lib/sqlpostgres/Cursor.rb, line 127 def scroll_declaration value = @opts.fetch(:scroll, :default) if value == :default nil elsif value "SCROLL" else "NO SCROLL" end end