Module: Sequel::Postgres::DatasetMethods

Included in:
DataObjects::Postgres::Dataset, JDBC::Postgres::Dataset, Dataset
Defined in:
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
DELETE_CLAUSE_METHODS = 
Dataset.clause_methods(:delete, %w'from using where')
EXCLUSIVE = 
'EXCLUSIVE'.freeze
EXPLAIN = 
'EXPLAIN '.freeze
EXPLAIN_ANALYZE = 
'EXPLAIN ANALYZE '.freeze
FOR_SHARE = 
' FOR SHARE'.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
UPDATE_CLAUSE_METHODS = 
Dataset.clause_methods(:update, %w'table set from where')

Class Method Summary collapse

Instance Method Summary collapse

Class Method Details

.extended(obj) ⇒ Object

Add the disable_insert_returning! mutation method



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

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

.included(mod) ⇒ Object

Add the disable_insert_returning! mutation method



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

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

Instance Method Details

#analyze ⇒ Object

Return the results of an ANALYZE query as a string



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

def analyze
  explain(:analyze=>true)
end

#disable_insert_returning ⇒ Object

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



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

def disable_insert_returning
  clone(:disable_insert_returning=>true)
end

#explain(opts = {}) ⇒ Object

Return the results of an EXPLAIN query as a string



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

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

#for_share ⇒ Object

Return a cloned dataset which will use FOR SHARE to lock returned rows.



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

def for_share
  lock_style(:share)
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).



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

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.



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

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.



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

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



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

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, opts = {}) ⇒ Object

Locks all tables in the dataset’s FROM clause (but not in JOINs) with the specified mode (e.g. ‘EXCLUSIVE’). If a block is given, starts a new transaction, locks the table, and yields. If a block is not given just locks the tables. Note that PostgreSQL will probably raise an error if you lock the table outside of an existing transaction. Returns nil.



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

def lock(mode, opts={})
  if block_given? # perform locking inside a transaction and yield to block
    @db.transaction(opts){lock(mode, opts); yield}
  else
    @db.execute(LOCK % [source_list(@opts[:from]), mode], opts) # lock without a transaction
  end
  nil
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/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_distinct_on? ⇒ Boolean

DISTINCT ON is a PostgreSQL extension

Returns:

  • (Boolean) 


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

def supports_distinct_on?
  true
end

#supports_modifying_joins? ⇒ Boolean

PostgreSQL supports modifying joined datasets

Returns:

  • (Boolean) 


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

def supports_modifying_joins?
  true
end

#supports_timestamp_timezones? ⇒ Boolean

PostgreSQL supports timezones in literal timestamps

Returns:

  • (Boolean) 


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

def supports_timestamp_timezones?
  true
end

#supports_window_functions? ⇒ Boolean

PostgreSQL 8.4+ supports window functions

Returns:

  • (Boolean) 


712
713
714
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 712

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.



717
718
719
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 717

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