Module: Sequel::Postgres::DatasetMethods

Included in:
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'delete from using where returning')
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 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 returning')
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

Instance Method Summary collapse

Instance Method Details

#analyze ⇒ Object

Return the results of an EXPLAIN ANALYZE query as a string



945
946
947
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 945

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



951
952
953
954
955
956
957
958
959
960
961
962
963
964
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 951

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

#explain(opts = {}) ⇒ Object

Return the results of an EXPLAIN query as a string



967
968
969
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 967

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.



972
973
974
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 972

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



978
979
980
981
982
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 978

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

#insert(*values) ⇒ Object

Insert given values into the database.



985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 985

def insert(*values)
  if @opts[:returning]
    # already know which columns to return, let the standard code
    # handle it
    super
  elsif @opts[:sql]
    # raw SQL used, so don't know which table is being inserted
    # into, and therefore can't determine primary key.  Run the
    # insert statement and return nil.
    super
    nil
  else
    # Force the use of RETURNING with the primary key value.
    returning(insert_pk).insert(*values){|r| return r.values.first}
  end
end

#insert_select(*values) ⇒ Object

Insert a record returning the record inserted



1003
1004
1005
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1003

def insert_select(*values)
  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.



1012
1013
1014
1015
1016
1017
1018
1019
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1012

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

PostgreSQL allows inserting multiple rows at once.



1022
1023
1024
1025
1026
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1022

def multi_insert_sql(columns, values)
  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) 


1030
1031
1032
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1030

def supports_cte_in_subqueries?
  supports_cte?
end

#supports_distinct_on? ⇒ Boolean

DISTINCT ON is a PostgreSQL extension

Returns:

  • (Boolean) 


1035
1036
1037
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1035

def supports_distinct_on?
  true
end

#supports_modifying_joins? ⇒ Boolean

PostgreSQL supports modifying joined datasets

Returns:

  • (Boolean) 


1040
1041
1042
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1040

def supports_modifying_joins?
  true
end

#supports_regexp? ⇒ Boolean

PostgreSQL supports pattern matching via regular expressions

Returns:

  • (Boolean) 


1050
1051
1052
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1050

def supports_regexp?
  true
end

#supports_returning?(type) ⇒ Boolean

Returning is always supported.

Returns:

  • (Boolean) 


1045
1046
1047
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1045

def supports_returning?(type)
  true
end

#supports_timestamp_timezones? ⇒ Boolean

PostgreSQL supports timezones in literal timestamps

Returns:

  • (Boolean) 


1055
1056
1057
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1055

def supports_timestamp_timezones?
  true
end

#supports_window_functions? ⇒ Boolean

PostgreSQL 8.4+ supports window functions

Returns:

  • (Boolean) 


1060
1061
1062
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1060

def supports_window_functions?
  server_version >= 80400
end

#truncate(opts = {}) ⇒ Object

Truncates the dataset. Returns nil.

Options:

:cascade

whether to use the CASCADE option, useful when truncating

tables with Foreign Keys.
:only

truncate using ONLY, so child tables are unaffected

:restart

use RESTART IDENTITY to restart any related sequences

:only and :restart only work correctly on PostgreSQL 8.4+.

Usage:

DB[:table].truncate # TRUNCATE TABLE "table"
# => nil
DB[:table].truncate(:cascade => true, :only=>true, :restart=>true) # TRUNCATE TABLE ONLY "table" RESTART IDENTITY CASCADE
# => nil



1079
1080
1081
1082
1083
1084
1085
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1079

def truncate(opts = {})
  if opts.empty?
    super()
  else
    clone(:truncate_opts=>opts).truncate
  end
end

#window(name, opts) ⇒ Object

Return a clone of the dataset with an addition named window that can be referenced in window functions.



1088
1089
1090
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1088

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