Class: Sequel::Postgres::Dataset
- Includes:
- DatasetMethods
- Defined in:
- lib/sequel/adapters/postgres.rb
Overview
Dataset class for PostgreSQL datasets that use the pg, postgres, or postgres-pr driver.
Defined Under Namespace
Modules: ArgumentMapper, BindArgumentMethods, PreparedStatementMethods
Constant Summary collapse
- APOS =
Sequel::Dataset::APOS
- DEFAULT_CURSOR_NAME =
'sequel_cursor'.freeze
- PREPARED_ARG_PLACEHOLDER =
LiteralString.new('$').freeze
Constants included from DatasetMethods
Sequel::Postgres::DatasetMethods::ACCESS_EXCLUSIVE, Sequel::Postgres::DatasetMethods::ACCESS_SHARE, Sequel::Postgres::DatasetMethods::APOS_RE, Sequel::Postgres::DatasetMethods::AS, Sequel::Postgres::DatasetMethods::BACKSLASH, Sequel::Postgres::DatasetMethods::BLOB_RE, Sequel::Postgres::DatasetMethods::BOOL_FALSE, Sequel::Postgres::DatasetMethods::BOOL_TRUE, Sequel::Postgres::DatasetMethods::COMMA, Sequel::Postgres::DatasetMethods::COMMA_SEPARATOR, Sequel::Postgres::DatasetMethods::CRLF, Sequel::Postgres::DatasetMethods::DOUBLE_APOS, Sequel::Postgres::DatasetMethods::EMPTY_STRING, Sequel::Postgres::DatasetMethods::ESCAPE, Sequel::Postgres::DatasetMethods::EXCLUSIVE, Sequel::Postgres::DatasetMethods::EXPLAIN, Sequel::Postgres::DatasetMethods::EXPLAIN_ANALYZE, Sequel::Postgres::DatasetMethods::FOR_SHARE, Sequel::Postgres::DatasetMethods::FROM, Sequel::Postgres::DatasetMethods::LOCK_MODES, Sequel::Postgres::DatasetMethods::NULL, Sequel::Postgres::DatasetMethods::PAREN_CLOSE, Sequel::Postgres::DatasetMethods::PAREN_OPEN, Sequel::Postgres::DatasetMethods::PG_TIMESTAMP_FORMAT, Sequel::Postgres::DatasetMethods::QUERY_PLAN, Sequel::Postgres::DatasetMethods::ROW_EXCLUSIVE, Sequel::Postgres::DatasetMethods::ROW_SHARE, Sequel::Postgres::DatasetMethods::SELECT_VALUES, Sequel::Postgres::DatasetMethods::SHARE, Sequel::Postgres::DatasetMethods::SHARE_ROW_EXCLUSIVE, Sequel::Postgres::DatasetMethods::SHARE_UPDATE_EXCLUSIVE, Sequel::Postgres::DatasetMethods::SPACE, Sequel::Postgres::DatasetMethods::SQL_WITH_RECURSIVE, Sequel::Postgres::DatasetMethods::WINDOW, Sequel::Postgres::DatasetMethods::XOR_OP
Constants inherited from Dataset
Dataset::ACTION_METHODS, Dataset::ALL, Dataset::AND_SEPARATOR, Dataset::APOS_RE, Dataset::ARG_BLOCK_ERROR_MSG, Dataset::ARRAY_ACCESS_ERROR_MSG, Dataset::ARRAY_EMPTY, Dataset::AS, Dataset::ASC, Dataset::BACKSLASH, Dataset::BITCOMP_CLOSE, Dataset::BITCOMP_OPEN, Dataset::BITWISE_METHOD_MAP, Dataset::BOOL_FALSE, Dataset::BOOL_TRUE, Dataset::BRACKET_CLOSE, Dataset::BRACKET_OPEN, Dataset::CASE_ELSE, Dataset::CASE_END, Dataset::CASE_OPEN, Dataset::CASE_THEN, Dataset::CASE_WHEN, Dataset::CAST_OPEN, Dataset::COLON, Dataset::COLUMN_CHANGE_OPTS, Dataset::COLUMN_REF_RE1, Dataset::COLUMN_REF_RE2, Dataset::COLUMN_REF_RE3, Dataset::COMMA, Dataset::COMMA_SEPARATOR, Dataset::CONDITIONED_JOIN_TYPES, Dataset::CONDITION_FALSE, Dataset::CONDITION_TRUE, Dataset::COUNT_FROM_SELF_OPTS, Dataset::COUNT_OF_ALL_AS_COUNT, Dataset::DATASET_ALIAS_BASE_NAME, Dataset::DATETIME_SECFRACTION_ARG, Dataset::DEFAULT, Dataset::DEFAULT_VALUES, Dataset::DELETE, Dataset::DESC, Dataset::DISTINCT, Dataset::DOT, Dataset::DOUBLE_APOS, Dataset::DOUBLE_QUOTE, Dataset::EMULATED_FUNCTION_MAP, Dataset::EQUAL, Dataset::ESCAPE, Dataset::EXISTS, Dataset::EXTENSIONS, Dataset::EXTRACT, Dataset::FILTER, Dataset::FORMAT_DATE, Dataset::FORMAT_DATE_STANDARD, Dataset::FORMAT_OFFSET, Dataset::FORMAT_TIMESTAMP_RE, Dataset::FORMAT_USEC, Dataset::FOR_UPDATE, Dataset::FRAME_ALL, Dataset::FRAME_ROWS, Dataset::FROM, Dataset::FUNCTION_DISTINCT, Dataset::GROUP_BY, Dataset::HAVING, Dataset::IMPORT_ERROR_MSG, Dataset::INSERT, Dataset::INTO, Dataset::IS_LITERALS, Dataset::IS_OPERATORS, Dataset::JOIN_METHODS, Dataset::LATERAL, Dataset::LIKE_OPERATORS, Dataset::LIMIT, Dataset::MUTATION_METHODS, Dataset::NON_SQL_OPTIONS, Dataset::NOTIMPL_MSG, Dataset::NOT_SPACE, Dataset::NULL, Dataset::NULLS_FIRST, Dataset::NULLS_LAST, Dataset::N_ARITY_OPERATORS, Dataset::OFFSET, Dataset::ON, Dataset::ON_PAREN, Dataset::OPTS, Dataset::ORDER_BY, Dataset::ORDER_BY_NS, Dataset::OVER, Dataset::PAREN_CLOSE, Dataset::PAREN_OPEN, Dataset::PAREN_SPACE_OPEN, Dataset::PARTITION_BY, Dataset::QUALIFY_KEYS, Dataset::QUERY_METHODS, Dataset::QUESTION_MARK, Dataset::QUESTION_MARK_RE, Dataset::QUOTE, Dataset::QUOTE_RE, Dataset::REGEXP_OPERATORS, Dataset::RETURNING, Dataset::SELECT, Dataset::SET, Dataset::SPACE, Dataset::SPACE_WITH, Dataset::SQL_WITH, Dataset::STANDARD_TIMESTAMP_FORMAT, Dataset::TILDE, Dataset::TIMESTAMP_FORMAT, Dataset::TWO_ARITY_OPERATORS, Dataset::UNCONDITIONED_JOIN_TYPES, Dataset::UNDERSCORE, Dataset::UNION_ALL_SELECT, Dataset::UPDATE, Dataset::USING, Dataset::VALUES, Dataset::WHERE, Dataset::WILDCARD, Dataset::WITHIN_GROUP, Dataset::WITH_ORDINALITY
Instance Attribute Summary
Attributes inherited from Dataset
Instance Method Summary collapse
-
#call(type, bind_vars = OPTS, *values, &block) ⇒ Object
Execute the given type of statement with the hash of values.
-
#fetch_rows(sql) ⇒ Object
Yield all rows returned by executing the given SQL and converting the types.
-
#paged_each(opts = OPTS, &block) ⇒ Object
Use a cursor for paging.
-
#prepare(type, name = nil, *values) ⇒ Object
Prepare the given type of statement with the given name, and store it in the database to be called later.
-
#use_cursor(opts = OPTS) ⇒ Object
Uses a cursor for fetching records, instead of fetching the entire result set at once.
-
#where_current_of(cursor_name = DEFAULT_CURSOR_NAME) ⇒ Object
Replace the WHERE clause with one that uses CURRENT OF with the given cursor name (or the default cursor name).
Methods included from DatasetMethods
#analyze, #complex_expression_sql_append, #disable_insert_returning, #explain, #for_share, #full_text_search, #insert, #insert_select, #insert_select_sql, #lock, #supports_cte?, #supports_cte_in_subqueries?, #supports_distinct_on?, #supports_insert_select?, #supports_lateral_subqueries?, #supports_modifying_joins?, #supports_regexp?, #supports_returning?, #supports_timestamp_timezones?, #supports_window_functions?, #truncate, #window
Methods inherited from Dataset
#<<, #==, #[], #add_graph_aliases, #aliased_expression_sql_append, #all, #and, #array_sql_append, #avg, #bind, #boolean_constant_sql_append, #case_expression_sql_append, #cast_sql_append, clause_methods, #clone, #column_all_sql_append, #columns, #columns!, #complex_expression_sql_append, #constant_sql_append, #count, #current_datetime, def_mutation_method, def_sql_method, #delayed_evaluation_sql_append, #delete, #distinct, #dup, #each, #each_server, #empty?, #eql?, #escape_like, #except, #exclude, #exclude_having, #exclude_where, #exists, #extension, #extension!, #filter, #first, #first!, #first_source, #first_source_alias, #first_source_table, #for_update, #freeze, #from, #from_self, #from_self!, #frozen?, #function_sql_append, #get, #graph, #grep, #group, #group_and_count, #group_by, #group_cube, #group_rollup, #hash, #having, #identifier_input_method, #identifier_input_method=, #identifier_output_method, #identifier_output_method=, #import, #initialize, #insert, #insert_sql, #inspect, #intersect, #interval, #invert, #join, #join_clause_sql_append, #join_on_clause_sql_append, #join_table, #join_using_clause_sql_append, #joined_dataset?, #last, #lateral, #limit, #literal_append, #lock_style, #map, #max, #min, #multi_insert, #multi_insert_sql, #naked, #naked!, #negative_boolean_constant_sql_append, #offset, #or, #order, #order_append, #order_by, #order_more, #order_prepend, #ordered_expression_sql_append, #placeholder_literal_string_sql_append, #provides_accurate_rows_matched?, #qualified_identifier_sql_append, #qualify, #quote_identifier_append, #quote_identifiers=, #quote_identifiers?, #quote_schema_table_append, #quoted_identifier_append, #range, #recursive_cte_requires_column_aliases?, register_extension, #requires_placeholder_type_specifiers?, #requires_sql_standard_datetimes?, #returning, #reverse, #reverse_order, #row_number_column, #schema_and_table, #select, #select_all, #select_append, #select_group, #select_hash, #select_hash_groups, #select_map, #select_more, #select_order_map, #server, #set_graph_aliases, #single_record, #single_value, #split_alias, #split_qualifiers, #sql, #subscript_sql_append, #sum, #supports_cte?, #supports_cte_in_subqueries?, #supports_derived_column_lists?, #supports_distinct_on?, #supports_group_cube?, #supports_group_rollup?, #supports_insert_select?, #supports_intersect_except?, #supports_intersect_except_all?, #supports_is_true?, #supports_join_using?, #supports_lateral_subqueries?, #supports_limits_in_correlated_subqueries?, #supports_modifying_joins?, #supports_multiple_column_in?, #supports_offsets_in_correlated_subqueries?, #supports_ordered_distinct_on?, #supports_regexp?, #supports_replace?, #supports_returning?, #supports_select_all_and_column?, #supports_timestamp_timezones?, #supports_timestamp_usecs?, #supports_where_true?, #supports_window_functions?, #to_hash, #to_hash_groups, #truncate, #truncate_sql, #unbind, #unfiltered, #ungraphed, #ungrouped, #union, #unlimited, #unordered, #unqualified_column_for, #unused_table_alias, #update, #update_sql, #where, #window_sql_append, #with, #with_recursive, #with_sql, #with_sql_all, #with_sql_delete, #with_sql_each, #with_sql_first, #with_sql_insert, #with_sql_single_value
Methods included from Metaprogramming
Methods included from SQL::StringMethods
Methods included from SQL::OrderMethods
Methods included from SQL::NumericMethods
Methods included from SQL::ComplexExpressionMethods
#extract, #sql_boolean, #sql_number, #sql_string
Methods included from SQL::CastMethods
#cast, #cast_numeric, #cast_string
Methods included from SQL::BooleanMethods
Methods included from SQL::AliasMethods
Constructor Details
This class inherits a constructor from Sequel::Dataset
Instance Method Details
#call(type, bind_vars = OPTS, *values, &block) ⇒ Object
Execute the given type of statement with the hash of values.
760 761 762 763 764 |
# File 'lib/sequel/adapters/postgres.rb', line 760 def call(type, bind_vars=OPTS, *values, &block) ps = to_prepared_statement(type, values) ps.extend(BindArgumentMethods) ps.call(bind_vars, &block) end |
#fetch_rows(sql) ⇒ Object
Yield all rows returned by executing the given SQL and converting the types.
634 635 636 637 |
# File 'lib/sequel/adapters/postgres.rb', line 634 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 |
#paged_each(opts = OPTS, &block) ⇒ Object
Use a cursor for paging.
640 641 642 |
# File 'lib/sequel/adapters/postgres.rb', line 640 def paged_each(opts=OPTS, &block) use_cursor(opts).each(&block) end |
#prepare(type, name = nil, *values) ⇒ Object
Prepare the given type of statement with the given name, and store it in the database to be called later.
768 769 770 771 772 773 774 775 776 |
# File 'lib/sequel/adapters/postgres.rb', line 768 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 |
#use_cursor(opts = OPTS) ⇒ Object
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 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.
664 665 666 |
# File 'lib/sequel/adapters/postgres.rb', line 664 def use_cursor(opts=OPTS) clone(:cursor=>{:rows_per_fetch=>1000}.merge(opts)) end |
#where_current_of(cursor_name = DEFAULT_CURSOR_NAME) ⇒ Object
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
676 677 678 |
# File 'lib/sequel/adapters/postgres.rb', line 676 def where_current_of(cursor_name=DEFAULT_CURSOR_NAME) clone(:where=>Sequel.lit(['CURRENT OF '], Sequel.identifier(cursor_name))) end |