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



374
375
376
377
378
379
380
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 374

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



383
384
385
386
387
388
389
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 383

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.



392
393
394
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 392

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

#for_update ⇒ Object

Return a cloned dataset with a :update lock type.



397
398
399
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 397

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



403
404
405
406
407
408
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 403

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

#insert(*values) ⇒ Object

Insert given values into the database.



411
412
413
414
415
416
417
418
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 411

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.



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

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



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

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.



432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 432

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



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

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.



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

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

#quoted_identifier(c) ⇒ Object

PostgreSQL assumes unquoted identifiers are lower case by default, so do not upcase the identifier when quoting it.



475
476
477
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 475

def quoted_identifier(c)
  "\"#{c}\""
end

#select_sql(opts = nil) ⇒ Object

Support lock mode, allowing FOR SHARE and FOR UPDATE queries.



480
481
482
483
484
485
486
487
488
489
490
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 480

def select_sql(opts = nil)
  row_lock_mode = opts ? opts[:lock] : @opts[:lock]
  sql = super
  case row_lock_mode
  when :update
    sql << FOR_UPDATE
  when :share
    sql << FOR_SHARE
  end
  sql
end