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 intersect union except 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



417
418
419
420
421
422
423
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 417

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



426
427
428
429
430
431
432
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 426

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.



435
436
437
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 435

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

#for_update ⇒ Object

Return a cloned dataset with a :update lock type.



440
441
442
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 440

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



446
447
448
449
450
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 446

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.



453
454
455
456
457
458
459
460
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 453

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.



463
464
465
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 463

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



468
469
470
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 468

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.



474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 474

def literal(v)
  case v
  when LiteralString
    v
  when SQL::Blob
    db.synchronize{|c| "E'#{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.



496
497
498
499
500
501
502
503
504
505
506
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 496

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.



509
510
511
512
513
514
515
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 509

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