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
EXCLUSIVE = 
'EXCLUSIVE'.freeze
EXPLAIN = 
'EXPLAIN '.freeze
EXPLAIN_ANALYZE = 
'EXPLAIN ANALYZE '.freeze
FOR_SHARE = 
' FOR SHARE'.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
SHARE = 
'SHARE'.freeze
SHARE_ROW_EXCLUSIVE = 
'SHARE ROW EXCLUSIVE'.freeze
SHARE_UPDATE_EXCLUSIVE = 
'SHARE UPDATE EXCLUSIVE'.freeze
SQL_WITH_RECURSIVE = 
"WITH RECURSIVE ".freeze
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
SELECT_VALUES = 
"VALUES ".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



1218
1219
1220
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1218

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.



1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1225

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

#disable_insert_returning ⇒ Object

Disables automatic use of INSERT … RETURNING. You can still use returning manually to force the use of RETURNING when inserting.

This is designed for cases where INSERT RETURNING cannot be used, such as when you are using partitioning with trigger functions or conditional rules, or when you are using a PostgreSQL version less than 8.2, or a PostgreSQL derivative that does not support returning.

Note that when this method is used, insert will not return the primary key of the inserted row, you will have to get the primary key of the inserted row before inserting via nextval, or after inserting via currval or lastval (making sure to use the same database connection for currval or lastval).



1262
1263
1264
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1262

def disable_insert_returning
  clone(:disable_insert_returning=>true)
end

#explain(opts = OPTS) ⇒ Object

Return the results of an EXPLAIN query as a string



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

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.



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

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.

:rank

Set to true to order by the rank, so that closer matches are returned first.



1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1287

def full_text_search(cols, terms, opts = OPTS)
  lang = Sequel.cast(opts[:language] || 'simple', :regconfig)
  terms = terms.join(' | ') if terms.is_a?(Array)
  columns = full_text_string_join(cols)
  query_func = (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery
  vector = Sequel.function(:to_tsvector, lang, columns)
  query = Sequel.function(query_func, lang, terms)

  ds = where(Sequel.lit(["(", " @@ ", ")"], vector, query))

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

  if opts[:rank]
    ds = ds.order{ts_rank_cd(vector, query)}
  end

  ds
end

#insert(*values) ⇒ Object

Insert given values into the database.



1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1309

def insert(*values)
  if @opts[:returning]
    # Already know which columns to return, let the standard code handle it
    super
  elsif @opts[:sql] || @opts[:disable_insert_returning]
    # Raw SQL used or RETURNING disabled, just use the default behavior
    # and return nil since sequence is not known.
    super
    nil
  else
    # Force the use of RETURNING with the primary key value,
    # unless it has been disabled.
    returning(insert_pk).insert(*values){|r| return r.values.first}
  end
end

#insert_select(*values) ⇒ Object

Insert a record returning the record inserted. Always returns nil without inserting a query if disable_insert_returning is used.



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

def insert_select(*values)
  return unless supports_insert_select?
  server?(:default).with_sql_first(insert_select_sql(*values))
end

#insert_select_sql(*values) ⇒ Object

The SQL to use for an insert_select, adds a RETURNING clause to the insert unless the RETURNING clause is already present.



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

def insert_select_sql(*values)
  ds = opts[:returning] ? self : returning
  ds.insert_sql(*values)
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.



1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1344

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

#supports_cte?(type = :select) ⇒ Boolean

Returns:

  • (Boolean) 


1360
1361
1362
1363
1364
1365
1366
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1360

def supports_cte?(type=:select)
  if type == :select
    server_version >= 80400
  else
    server_version >= 90100
  end
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) 


1370
1371
1372
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1370

def supports_cte_in_subqueries?
  supports_cte?
end

#supports_distinct_on? ⇒ Boolean

DISTINCT ON is a PostgreSQL extension

Returns:

  • (Boolean) 


1375
1376
1377
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1375

def supports_distinct_on?
  true
end

#supports_insert_select? ⇒ Boolean

True unless insert returning has been disabled for this dataset.

Returns:

  • (Boolean) 


1380
1381
1382
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1380

def supports_insert_select?
  !@opts[:disable_insert_returning]
end

#supports_lateral_subqueries? ⇒ Boolean

PostgreSQL 9.3rc1+ supports lateral subqueries

Returns:

  • (Boolean) 


1385
1386
1387
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1385

def supports_lateral_subqueries?
  server_version >= 90300
end

#supports_modifying_joins? ⇒ Boolean

PostgreSQL supports modifying joined datasets

Returns:

  • (Boolean) 


1390
1391
1392
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1390

def supports_modifying_joins?
  true
end

#supports_regexp? ⇒ Boolean

PostgreSQL supports pattern matching via regular expressions

Returns:

  • (Boolean) 


1400
1401
1402
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1400

def supports_regexp?
  true
end

#supports_returning?(type) ⇒ Boolean

Returning is always supported.

Returns:

  • (Boolean) 


1395
1396
1397
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1395

def supports_returning?(type)
  true
end

#supports_timestamp_timezones? ⇒ Boolean

PostgreSQL supports timezones in literal timestamps

Returns:

  • (Boolean) 


1405
1406
1407
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1405

def supports_timestamp_timezones?
  true
end

#supports_window_functions? ⇒ Boolean

PostgreSQL 8.4+ supports window functions

Returns:

  • (Boolean) 


1410
1411
1412
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1410

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



1429
1430
1431
1432
1433
1434
1435
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1429

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.



1438
1439
1440
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1438

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