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')
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
ESCAPE = 
Dataset::ESCAPE
BACKSLASH = 
Dataset::BACKSLASH
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
LOCK_MODES = 
['ACCESS SHARE', 'ROW SHARE', 'ROW EXCLUSIVE', 'SHARE UPDATE EXCLUSIVE', 'SHARE', 'SHARE ROW EXCLUSIVE', 'EXCLUSIVE', 'ACCESS EXCLUSIVE'].each{|s| s.freeze}

Instance Method Summary collapse

Instance Method Details

#analyze ⇒ Object

Return the results of an EXPLAIN ANALYZE query as a string



1156
1157
1158
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1156

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 (#), and use the ILIKE and NOT ILIKE operators.



1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1163

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
  when :ILIKE, :'NOT ILIKE'
    sql << PAREN_OPEN
    literal_append(sql, args.at(0))
    sql << SPACE << op.to_s << SPACE
    literal_append(sql, args.at(1))
    sql << ESCAPE
    literal_append(sql, BACKSLASH)
    sql << PAREN_CLOSE
  else
    super
  end
end

#explain(opts = OPTS) ⇒ Object

Return the results of an EXPLAIN query as a string



1187
1188
1189
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1187

def explain(opts=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.



1192
1193
1194
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1192

def for_share
  lock_style(:share)
end

#full_text_search(cols, terms, opts = OPTS) ⇒ Object

Run a full text search on PostgreSQL. By default, searching for the inclusion of any of the terms in any of the cols.

Options:

:language

The language to use for the search (default: ‘simple’)

:plain

Whether a plain search should be used (default: false). In this case, terms should be a single string, and it will do a search where cols contains all of the words in terms. This ignores search operators in terms.

:phrase

Similar to :plain, but also adding an ILIKE filter to ensure that returned rows also include the exact phrase used.



1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1206

def full_text_search(cols, terms, opts = OPTS)
  lang = opts[:language] || 'simple'
  terms = terms.join(' | ') if terms.is_a?(Array)
  to_tsquery = (opts[:phrase] || opts[:plain]) ? 'plainto_tsquery' : 'to_tsquery'

  ds = where(Sequel.lit(["(to_tsvector(", "::regconfig, ", ") @@ #{to_tsquery}(", "::regconfig, ", "))"], lang, full_text_string_join(cols), lang, terms))

  if opts[:phrase]
    ds = ds.grep(cols, "%#{escape_like(terms)}%", :case_insensitive=>true)
  end

  ds
end

#insert(*values) ⇒ Object

Insert given values into the database.



1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1221

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



1239
1240
1241
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1239

def insert_select(*values)
  returning.insert(*values){|r| return r}
end

#lock(mode, opts = 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.



1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1248

def lock(mode, opts=OPTS)
  if block_given? # perform locking inside a transaction and yield to block
    @db.transaction(opts){lock(mode, opts); yield}
  else
    sql = 'LOCK TABLE '
    source_list_append(sql, @opts[:from])
    mode = mode.to_s.upcase.strip
    unless LOCK_MODES.include?(mode)
      raise Error, "Unsupported lock mode: #{mode}"
    end
    sql << " IN #{mode} MODE"
    @db.execute(sql, opts)
  end
  nil
end

#multi_insert_sql(columns, values) ⇒ Object

PostgreSQL allows inserting multiple rows at once.



1265
1266
1267
1268
1269
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1265

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) 


1273
1274
1275
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1273

def supports_cte_in_subqueries?
  supports_cte?
end

#supports_distinct_on? ⇒ Boolean

DISTINCT ON is a PostgreSQL extension

Returns:

  • (Boolean) 


1278
1279
1280
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1278

def supports_distinct_on?
  true
end

#supports_lateral_subqueries? ⇒ Boolean

PostgreSQL 9.3rc1+ supports lateral subqueries

Returns:

  • (Boolean) 


1283
1284
1285
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1283

def supports_lateral_subqueries?
  server_version >= 90300
end

#supports_modifying_joins? ⇒ Boolean

PostgreSQL supports modifying joined datasets

Returns:

  • (Boolean) 


1288
1289
1290
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1288

def supports_modifying_joins?
  true
end

#supports_regexp? ⇒ Boolean

PostgreSQL supports pattern matching via regular expressions

Returns:

  • (Boolean) 


1298
1299
1300
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1298

def supports_regexp?
  true
end

#supports_returning?(type) ⇒ Boolean

Returning is always supported.

Returns:

  • (Boolean) 


1293
1294
1295
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1293

def supports_returning?(type)
  true
end

#supports_timestamp_timezones? ⇒ Boolean

PostgreSQL supports timezones in literal timestamps

Returns:

  • (Boolean) 


1303
1304
1305
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1303

def supports_timestamp_timezones?
  true
end

#supports_window_functions? ⇒ Boolean

PostgreSQL 8.4+ supports window functions

Returns:

  • (Boolean) 


1308
1309
1310
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1308

def supports_window_functions?
  server_version >= 80400
end

#truncate(opts = 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



1327
1328
1329
1330
1331
1332
1333
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1327

def truncate(opts = 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.



1336
1337
1338
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1336

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