Module: Sequel::Postgres::DatasetMethods

Included in:
Dataset
Defined in:
lib/sequel_core/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
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_ORDER = 
%w'distinct columns from join where group having compounds order limit lock'.freeze
SHARE = 
'SHARE'.freeze
SHARE_ROW_EXCLUSIVE = 
'SHARE ROW EXCLUSIVE'.freeze
SHARE_UPDATE_EXCLUSIVE = 
'SHARE UPDATE EXCLUSIVE'.freeze

Instance Method Summary collapse

Instance Method Details

#analyze(opts = nil) ⇒ Object

Return the results of an ANALYZE query as a string



575
576
577
578
579
580
581
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 575

def analyze(opts = nil)
  analysis = []
  fetch_rows(EXPLAIN_ANALYZE + select_sql(opts)) do |r|
    analysis << r[QUERY_PLAN]
  end
  analysis.join("\r\n")
end

#explain(opts = nil) ⇒ Object

Return the results of an EXPLAIN query as a string



584
585
586
587
588
589
590
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 584

def explain(opts = nil)
  analysis = []
  fetch_rows(EXPLAIN + select_sql(opts)) do |r|
    analysis << r[QUERY_PLAN]
  end
  analysis.join("\r\n")
end

#for_share ⇒ Object

Return a cloned dataset with a :share lock type.



593
594
595
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 593

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

#for_update ⇒ Object

Return a cloned dataset with a :update lock type.



598
599
600
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 598

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



604
605
606
607
608
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 604

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

#insert(*values) ⇒ Object

Insert given values into the database.



611
612
613
614
615
616
617
618
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 611

def insert(*values)
  if !@opts[:sql] and server_version >= 80200
    single_value(:sql=>insert_returning_pk_sql(*values))
  else
    execute_insert(insert_sql(*values), :table=>opts[:from].first,
      :values=>values.size == 1 ? values.first : values)
  end
end

#insert_returning_sql(returning, *values) ⇒ Object

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



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

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



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

def insert_select(*values)
  single_record(:naked=>true, :sql=>insert_returning_sql(nil, *values)) if server_version >= 80200
end

#literal(v) ⇒ Object

Handle microseconds for Time and DateTime values, as well as PostgreSQL specific boolean values and string escaping.



632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 632

def literal(v)
  case v
  when LiteralString
    v
  when SQL::Blob
    db.synchronize{|c| "'#{c.escape_bytea(v)}'"}
  when String
    db.synchronize{|c| "'#{c.escape_string(v)}'"}
  when Time
    "#{v.strftime(PG_TIMESTAMP_FORMAT)}.#{sprintf("%06d",v.usec)}'"
  when DateTime
    "#{v.strftime(PG_TIMESTAMP_FORMAT)}.#{sprintf("%06d", (v.sec_fraction * 86400000000).to_i)}'"
  when TrueClass
    BOOL_TRUE
  when FalseClass
    BOOL_FALSE
  else
    super
  end
end

#lock(mode, server = nil) ⇒ Object

Locks the table with the specified mode.



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

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.



667
668
669
670
671
672
673
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 667

def multi_insert_sql(columns, values)
  return super if server_version < 80200
  
  # postgresql 8.2 introduces support for multi-row insert
  values = values.map {|r| literal(Array(r))}.join(COMMA_SEPARATOR)
  ["INSERT INTO #{source_list(@opts[:from])} (#{identifier_list(columns)}) VALUES #{values}"]
end