Module: Sequel::Postgres::DatasetMethods

Included in:
DataObjects::Postgres::Dataset, JDBC::Postgres::Dataset, Dataset
Defined in:
lib/sequel/lib/sequel/adapters/shared/postgres.rb

Overview

Instance methods for datasets that connect to a PostgreSQL database.

Defined Under Namespace

Modules: PreparedStatementMethods

Constant Summary collapse

ACCESS_SHARE =
'ACCESS SHARE'.freeze
ACCESS_EXCLUSIVE =
'ACCESS EXCLUSIVE'.freeze
BOOL_FALSE =
'false'.freeze
BOOL_TRUE =
'true'.freeze
COMMA_SEPARATOR =
', '.freeze
EXCLUSIVE =
'EXCLUSIVE'.freeze
EXPLAIN =
'EXPLAIN '.freeze
EXPLAIN_ANALYZE =
'EXPLAIN ANALYZE '.freeze
FOR_SHARE =
' FOR SHARE'.freeze
FOR_UPDATE =
' FOR UPDATE'.freeze
LOCK =
'LOCK TABLE %s IN %s MODE'.freeze
NULL =
LiteralString.new('NULL').freeze
PG_TIMESTAMP_FORMAT =
"TIMESTAMP '%Y-%m-%d %H:%M:%S".freeze
QUERY_PLAN =
'QUERY PLAN'.to_sym
ROW_EXCLUSIVE =
'ROW EXCLUSIVE'.freeze
ROW_SHARE =
'ROW SHARE'.freeze
SELECT_CLAUSE_METHODS =
Dataset.clause_methods(:select, %w'distinct columns from join where group having compounds order limit lock')
SELECT_CLAUSE_METHODS_84 =
Dataset.clause_methods(:select, %w'with distinct columns from join where group having window compounds order limit lock')
SHARE =
'SHARE'.freeze
SHARE_ROW_EXCLUSIVE =
'SHARE ROW EXCLUSIVE'.freeze
SHARE_UPDATE_EXCLUSIVE =
'SHARE UPDATE EXCLUSIVE'.freeze
SQL_WITH_RECURSIVE =
"WITH RECURSIVE ".freeze

Class Method Summary collapse

Instance Method Summary collapse

Class Method Details

.extended(obj) ⇒ Object

Add the disable_insert_returning! mutation method



612
613
614
# File 'lib/sequel/lib/sequel/adapters/shared/postgres.rb', line 612

def self.extended(obj)
  obj.def_mutation_method(:disable_insert_returning)
end

.included(mod) ⇒ Object

Add the disable_insert_returning! mutation method



617
618
619
# File 'lib/sequel/lib/sequel/adapters/shared/postgres.rb', line 617

def self.included(mod)
  mod.def_mutation_method(:disable_insert_returning)
end

Instance Method Details

#analyzeObject

Return the results of an ANALYZE query as a string



622
623
624
# File 'lib/sequel/lib/sequel/adapters/shared/postgres.rb', line 622

def analyze
  explain(:analyze=>true)
end

#disable_insert_returningObject

Disable the use of INSERT RETURNING, even if the server supports it



627
628
629
# File 'lib/sequel/lib/sequel/adapters/shared/postgres.rb', line 627

def disable_insert_returning
  clone(:disable_insert_returning=>true)
end

#explain(opts = {}) ⇒ Object

Return the results of an EXPLAIN query as a string



632
633
634
# File 'lib/sequel/lib/sequel/adapters/shared/postgres.rb', line 632

def explain(opts={})
  with_sql((opts[:analyze] ? EXPLAIN_ANALYZE : EXPLAIN) + select_sql).map(QUERY_PLAN).join("\r\n")
end

#for_shareObject

Return a cloned dataset with a :share lock type.



637
638
639
# File 'lib/sequel/lib/sequel/adapters/shared/postgres.rb', line 637

def for_share
  clone(:lock => :share)
end

#for_updateObject

Return a cloned dataset with a :update lock type.



642
643
644
# File 'lib/sequel/lib/sequel/adapters/shared/postgres.rb', line 642

def for_update
  clone(:lock => :update)
end

#full_text_search(cols, terms, opts = {}) ⇒ Object

PostgreSQL specific full text search syntax, using tsearch2 (included in 8.3 by default, and available for earlier versions as an add-on).



648
649
650
651
# File 'lib/sequel/lib/sequel/adapters/shared/postgres.rb', line 648

def full_text_search(cols, terms, opts = {})
  lang = opts[:language] || 'simple'
  filter("to_tsvector(#{literal(lang)}, #{full_text_string_join(cols)}) @@ to_tsquery(#{literal(lang)}, #{literal(Array(terms).join(' | '))})")
end

#insert(*values) ⇒ Object

Insert given values into the database.



654
655
656
657
658
659
660
661
662
# File 'lib/sequel/lib/sequel/adapters/shared/postgres.rb', line 654

def insert(*values)
  if @opts[:sql]
    execute_insert(insert_sql(*values))
  elsif @opts[:disable_insert_returning] || server_version < 80200
    execute_insert(insert_sql(*values), :table=>opts[:from].first, :values=>values.size == 1 ? values.first : values)
  else
    clone(default_server_opts(:sql=>insert_returning_pk_sql(*values))).single_value
  end
end

#insert_returning_sql(returning, *values) ⇒ Object

Use the RETURNING clause to return the columns listed in returning.



665
666
667
# File 'lib/sequel/lib/sequel/adapters/shared/postgres.rb', line 665

def insert_returning_sql(returning, *values)
  "#{insert_sql(*values)} RETURNING #{column_list(Array(returning))}"
end

#insert_select(*values) ⇒ Object

Insert a record returning the record inserted



670
671
672
673
# File 'lib/sequel/lib/sequel/adapters/shared/postgres.rb', line 670

def insert_select(*values)
  return if opts[:disable_insert_returning] || server_version < 80200
  naked.clone(default_server_opts(:sql=>insert_returning_sql(nil, *values))).single_record
end

#lock(mode, server = nil) ⇒ Object

Locks the table with the specified mode.



676
677
678
679
680
681
682
683
684
685
686
# File 'lib/sequel/lib/sequel/adapters/shared/postgres.rb', line 676

def lock(mode, server=nil)
  sql = LOCK % [source_list(@opts[:from]), mode]
  @db.synchronize(server) do
    if block_given? # perform locking inside a transaction and yield to block
      @db.transaction(server){@db.execute(sql, :server=>server); yield}
    else
      @db.execute(sql, :server=>server) # lock without a transaction
      self
    end
  end
end

#multi_insert_sql(columns, values) ⇒ Object

For PostgreSQL version > 8.2, allow inserting multiple rows at once.



689
690
691
692
693
694
# File 'lib/sequel/lib/sequel/adapters/shared/postgres.rb', line 689

def multi_insert_sql(columns, values)
  return super if server_version < 80200
  
  # postgresql 8.2 introduces support for multi-row insert
  [insert_sql(columns, LiteralString.new('VALUES ' + values.map {|r| literal(Array(r))}.join(COMMA_SEPARATOR)))]
end

#supports_timestamp_timezones?Boolean

PostgreSQL supports timezones in literal timestamps

Returns:

  • (Boolean)


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

def supports_timestamp_timezones?
  true
end

#supports_window_functions?Boolean

PostgreSQL 8.4+ supports window functions

Returns:

  • (Boolean)


702
703
704
# File 'lib/sequel/lib/sequel/adapters/shared/postgres.rb', line 702

def supports_window_functions?
  server_version >= 80400
end

#window(name, opts) ⇒ Object

Return a clone of the dataset with an addition named window that can be referenced in window functions.



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

def window(name, opts)
  clone(:window=>(@opts[:windows]||[]) + [[name, SQL::Window.new(opts)]])
end