Module: Sequel::Postgres::DatasetMethods

Included in:
DataObjects::Postgres::Dataset, JDBC::Postgres::Dataset, Dataset, Swift::Postgres::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'delete from using where')
DELETE_CLAUSE_METHODS_91 = 
Dataset.clause_methods(:delete, %w'with delete from using where returning')
EXCLUSIVE = 
'EXCLUSIVE'.freeze
EXPLAIN = 
'EXPLAIN '.freeze
EXPLAIN_ANALYZE = 
'EXPLAIN ANALYZE '.freeze
FOR_SHARE = 
' FOR SHARE'.freeze
INSERT_CLAUSE_METHODS = 
Dataset.clause_methods(:insert, %w'insert into columns values')
INSERT_CLAUSE_METHODS_82 = 
Dataset.clause_methods(:insert, %w'insert into columns values returning')
INSERT_CLAUSE_METHODS_91 = 
Dataset.clause_methods(:insert, %w'with insert into columns values returning')
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'select distinct columns from join where group having compounds order limit lock')
SELECT_CLAUSE_METHODS_84 = 
Dataset.clause_methods(:select, %w'with select 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'update table set from where')
UPDATE_CLAUSE_METHODS_91 = 
Dataset.clause_methods(:update, %w'with update table set from where returning')
SPACE = 
Dataset::SPACE
FROM = 
Dataset::FROM
APOS = 
Dataset::APOS
APOS_RE = 
Dataset::APOS_RE
DOUBLE_APOS = 
Dataset::DOUBLE_APOS
PAREN_OPEN = 
Dataset::PAREN_OPEN
PAREN_CLOSE = 
Dataset::PAREN_CLOSE
COMMA = 
Dataset::COMMA
AS = 
Dataset::AS
XOR_OP = 
' # '.freeze
CRLF = 
"\r\n".freeze
BLOB_RE = 
/[\000-\037\047\134\177-\377]/n.freeze
WINDOW = 
" WINDOW ".freeze
EMPTY_STRING = 
''.freeze

Class Method Summary collapse

Instance Method Summary collapse

Class Method Details

.extended(obj) ⇒ Object

Add the disable_insert_returning! mutation method



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

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

.included(mod) ⇒ Object

Add the disable_insert_returning! mutation method



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

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



722
723
724
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 722

def analyze
  explain(:analyze=>true)
end

#complex_expression_sql_append(sql, op, args) ⇒ Object

Handle converting the ruby xor operator (^) into the PostgreSQL xor operator (#).



728
729
730
731
732
733
734
735
736
737
738
739
740
741
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 728

def complex_expression_sql_append(sql, op, args)
  case op
  when :^
    j = XOR_OP
    c = false
    args.each do |a|
      sql << j if c
      literal_append(sql, a)
      c ||= true
    end
  else
    super
  end
end

#disable_insert_returning ⇒ Object

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



744
745
746
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 744

def disable_insert_returning
  clone(:disable_insert_returning=>true)
end

#explain(opts = {}) ⇒ Object

Return the results of an EXPLAIN query as a string



749
750
751
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 749

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

#for_share ⇒ Object

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



754
755
756
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 754

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).



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

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

#insert(*values, &block) ⇒ Object

Insert given values into the database.



767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 767

def insert(*values, &block)
  if @opts[:returning]
    super
  elsif !@opts[:sql] && supports_insert_select?
    returning(insert_pk).insert(*values){|r| return r.values.first}
  elsif (f = opts[:from]) && !f.empty?
    v = if values.size == 1
      values.first
    elsif values.size == 2 && values.all?{|v0| v0.is_a?(Array)}
      Hash[*values.first.zip(values.last).flatten]
    else
      values
    end
    execute_insert(insert_sql(*values), :table=>f.first, :values=>v)
  else
    super
  end
end

#insert_select(*values) ⇒ Object

Insert a record returning the record inserted



787
788
789
790
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 787

def insert_select(*values)
  return unless supports_insert_select?
  returning.insert(*values){|r| return r}
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.



797
798
799
800
801
802
803
804
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 797

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.



807
808
809
810
811
812
813
814
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 807

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

#supports_cte_in_subqueries? ⇒ Boolean

PostgreSQL supports using the WITH clause in subqueries if it supports using WITH at all (i.e. on PostgreSQL 8.4+).

Returns:

  • (Boolean) 


818
819
820
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 818

def supports_cte_in_subqueries?
  supports_cte?
end

#supports_distinct_on? ⇒ Boolean

DISTINCT ON is a PostgreSQL extension

Returns:

  • (Boolean) 


823
824
825
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 823

def supports_distinct_on?
  true
end

#supports_modifying_joins? ⇒ Boolean

PostgreSQL supports modifying joined datasets

Returns:

  • (Boolean) 


828
829
830
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 828

def supports_modifying_joins?
  true
end

#supports_returning?(type) ⇒ Boolean

Returns:

  • (Boolean) 


832
833
834
835
836
837
838
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 832

def supports_returning?(type)
  if type == :insert
    server_version >= 80200 && !opts[:disable_insert_returning]
  else
    server_version >= 80200
  end
end

#supports_timestamp_timezones? ⇒ Boolean

PostgreSQL supports timezones in literal timestamps

Returns:

  • (Boolean) 


841
842
843
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 841

def supports_timestamp_timezones?
  true
end

#supports_window_functions? ⇒ Boolean

PostgreSQL 8.4+ supports window functions

Returns:

  • (Boolean) 


846
847
848
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 846

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.



851
852
853
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 851

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