Class: Sequel::Postgres::Dataset

Inherits:
Dataset show all
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

Constant Summary collapse

APOS = 
Sequel::Dataset::APOS
DEFAULT_CURSOR_NAME = 
'sequel_cursor'.freeze
PREPARED_ARG_PLACEHOLDER = 
LiteralString.new('$').freeze
BindArgumentMethods = 
prepared_statements_module(:bind, [ArgumentMapper, ::Sequel::Postgres::DatasetMethods::PreparedStatementMethods], %w'execute execute_dui')
PreparedStatementMethods = 
prepared_statements_module(:prepare, BindArgumentMethods, %w'execute execute_dui') do
  # Raise a more obvious error if you attempt to call a unnamed prepared statement.
  def call(*)
    raise Error, "Cannot call prepared statement without a name" if prepared_statement_name.nil?
    super
  end
end

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_PREPARED_STATEMENT_MODULE_METHODS, Dataset::DEFAULT_VALUES, Dataset::DELETE, Dataset::DESC, Dataset::DISTINCT, Dataset::DOT, Dataset::DOUBLE_APOS, Dataset::DOUBLE_QUOTE, Dataset::EMPTY_PARENS, 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::PREPARED_STATEMENT_MODULE_CODE, 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

#db, #opts, #row_proc

Instance Method Summary collapse

Methods included from DatasetMethods

#analyze, #complex_expression_sql_append, #disable_insert_returning, #explain, #for_share, #full_text_search, #insert, #insert_conflict, #insert_ignore, #insert_select, #insert_select_sql, #lock, #supports_cte?, #supports_cte_in_subqueries?, #supports_distinct_on?, #supports_group_cube?, #supports_group_rollup?, #supports_grouping_sets?, #supports_insert_conflict?, #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_append, #group_by, #group_cube, #group_rollup, #grouping_sets, #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, #server?, #set_graph_aliases, #single_record, #single_record!, #single_value, #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_grouping_sets?, #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

#meta_def

Methods included from SQL::StringMethods

#ilike, #like

Methods included from SQL::OrderMethods

#asc, #desc

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

#as

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.



759
760
761
762
763
 
# File 'lib/sequel/adapters/postgres.rb', line 759

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.



666
667
668
669
 
# File 'lib/sequel/adapters/postgres.rb', line 666

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.



672
673
674
 
# File 'lib/sequel/adapters/postgres.rb', line 672

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.



767
768
769
770
771
772
773
774
775
 
# File 'lib/sequel/adapters/postgres.rb', line 767

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.



696
697
698
 
# File 'lib/sequel/adapters/postgres.rb', line 696

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



708
709
710
 
# File 'lib/sequel/adapters/postgres.rb', line 708

def where_current_of(cursor_name=DEFAULT_CURSOR_NAME)
  clone(:where=>Sequel.lit(['CURRENT OF '], Sequel.identifier(cursor_name)))
end