Module: Sequel::Postgres::DatasetMethods

Includes:
UnmodifiedIdentifiers::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.

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
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 = 
' '.freeze
FROM = 
' FROM '.freeze
APOS = 
"'".freeze
APOS_RE = 
/'/.freeze
DOUBLE_APOS = 
"''".freeze
PAREN_CLOSE = 
')'.freeze
PAREN_OPEN = 
'('.freeze
COMMA = 
', '.freeze
ESCAPE = 
" ESCAPE ".freeze
BACKSLASH = 
"\\".freeze
AS = 
' AS '.freeze
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
SKIP_LOCKED = 
" SKIP LOCKED".freeze
NULL = 
LiteralString.new('NULL').freeze
LOCK_MODES = 
['ACCESS SHARE', 'ROW SHARE', 'ROW EXCLUSIVE', 'SHARE UPDATE EXCLUSIVE', 'SHARE', 'SHARE ROW EXCLUSIVE', 'EXCLUSIVE', 'ACCESS EXCLUSIVE'].each(&:freeze)
NON_SQL_OPTIONS = 
(Dataset::NON_SQL_OPTIONS + [:cursor, :insert_conflict]).freeze

Instance Method Summary collapse

Instance Method Details

#analyze ⇒ Object

Return the results of an EXPLAIN ANALYZE query as a string



1319
1320
1321
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1319

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.



1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1326

def complex_expression_sql_append(sql, op, args)
  case op
  when :^
    j = ' # '
    c = false
    args.each do |a|
      sql << j if c
      literal_append(sql, a)
      c ||= true
    end
  when :ILIKE, :'NOT ILIKE'
    sql << '('
    literal_append(sql, args[0])
    sql << ' ' << op.to_s << ' '
    literal_append(sql, args[1])
    sql << " ESCAPE "
    literal_append(sql, "\\")
    sql << ')'
  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).



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

def disable_insert_returning
  clone(:disable_insert_returning=>true)
end

#explain(opts = OPTS) ⇒ Object

Return the results of an EXPLAIN query as a string



1368
1369
1370
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1368

def explain(opts=OPTS)
  with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map(:'QUERY PLAN').join("\r\n")
end

#for_share ⇒ Object

Return a cloned dataset which will use FOR SHARE to lock returned rows.



1373
1374
1375
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1373

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:

:headline

Append a expression to the selected columns aliased to headline that contains an extract of the matched text.

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

:to_tsquery

Can be set to :plain or :phrase to specify the function to use to convert the terms to a ts_query.

:tsquery

Specifies the terms argument is already a valid SQL expression returning a tsquery, and can be used directly in the query.

:tsvector

Specifies the cols argument is already a valid SQL expression returning a tsvector, and can be used directly in the query.



1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1396

def full_text_search(cols, terms, opts = OPTS)
  lang = Sequel.cast(opts[:language] || 'simple', :regconfig)

  unless opts[:tsvector]
    phrase_cols = full_text_string_join(cols)
    cols = Sequel.function(:to_tsvector, lang, phrase_cols)
  end

  unless opts[:tsquery]
    phrase_terms = terms.is_a?(Array) ? terms.join(' | ') : terms

    query_func = case to_tsquery = opts[:to_tsquery]
    when :phrase, :plain
      :"#{to_tsquery}to_tsquery"
    else
      (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery
    end

    terms = Sequel.function(query_func, lang, phrase_terms)
  end

  ds = where(Sequel.lit(["", " @@ ", ""], cols, terms))

  if opts[:phrase]
    raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery]
    ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true)
  end

  if opts[:rank]
    ds = ds.reverse{ts_rank_cd(cols, terms)}
  end

  if opts[:headline]
    ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)}
  end

  ds
end

#insert(*values) ⇒ Object

Insert given values into the database.



1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1436

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_conflict(opts = OPTS) ⇒ Object

Handle uniqueness violations when inserting, by updating the conflicting row, using ON CONFLICT. With no options, uses ON CONFLICT DO NOTHING. Options:

:conflict_where

The index filter, when using a partial index to determine uniqueness.

:constraint

An explicit constraint name, has precendence over :target.

:target

The column name or expression to handle uniqueness violations on.

:update

A hash of columns and values to set. Uses ON CONFLICT DO UPDATE.

:update_where

A WHERE condition to use for the update.

Examples:

DB[:table].insert_conflict.insert(:a=>1, :b=>2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT DO NOTHING

DB[:table].insert_conflict(:constraint=>:table_a_uidx).insert(:a=>1, :b=>2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT ON CONSTRAINT table_a_uidx DO NOTHING

DB[:table].insert_conflict(:target=>:a).insert(:a=>1, :b=>2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT (a) DO NOTHING

DB[:table].insert_conflict(:target=>:a, :conflict_where=>{:c=>true}).insert(:a=>1, :b=>2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT (a) WHERE (c IS TRUE) DO NOTHING

DB[:table].insert_conflict(:target=>:a, :update=>{:b=>:excluded__b}).insert(:a=>1, :b=>2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT (a) DO UPDATE SET b = excluded.b

DB[:table].insert_conflict(:constraint=>:table_a_uidx,
  :update=>{:b=>:excluded__b}, :update_where=>{:table__status_id=>1}).insert(:a=>1, :b=>2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT ON CONSTRAINT table_a_uidx
# DO UPDATE SET b = excluded.b WHERE (table.status_id = 1)



1487
1488
1489
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1487

def insert_conflict(opts=OPTS)
  clone(:insert_conflict => opts)
end

#insert_ignore ⇒ Object

Ignore uniqueness/exclusion violations when inserting, using ON CONFLICT DO NOTHING. Exists mostly for compatibility to MySQL’s insert_ignore. Example:

DB[:table].insert_ignore.insert(:a=>1, :b=>2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT DO NOTHING



1497
1498
1499
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1497

def insert_ignore
  insert_conflict
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.



1503
1504
1505
1506
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1503

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.



1510
1511
1512
1513
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1510

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.



1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1520

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


1536
1537
1538
1539
1540
1541
1542
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1536

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) 


1546
1547
1548
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1546

def supports_cte_in_subqueries?
  supports_cte?
end

#supports_distinct_on? ⇒ Boolean

DISTINCT ON is a PostgreSQL extension

Returns:

  • (Boolean) 


1551
1552
1553
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1551

def supports_distinct_on?
  true
end

#supports_group_cube? ⇒ Boolean

PostgreSQL 9.5+ supports GROUP CUBE

Returns:

  • (Boolean) 


1556
1557
1558
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1556

def supports_group_cube?
  server_version >= 90500
end

#supports_group_rollup? ⇒ Boolean

PostgreSQL 9.5+ supports GROUP ROLLUP

Returns:

  • (Boolean) 


1561
1562
1563
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1561

def supports_group_rollup?
  server_version >= 90500
end

#supports_grouping_sets? ⇒ Boolean

PostgreSQL 9.5+ supports GROUPING SETS

Returns:

  • (Boolean) 


1566
1567
1568
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1566

def supports_grouping_sets?
  server_version >= 90500
end

#supports_insert_conflict? ⇒ Boolean

PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.

Returns:

  • (Boolean) 


1576
1577
1578
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1576

def supports_insert_conflict?
  server_version >= 90500
end

#supports_insert_select? ⇒ Boolean

True unless insert returning has been disabled for this dataset.

Returns:

  • (Boolean) 


1571
1572
1573
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1571

def supports_insert_select?
  !@opts[:disable_insert_returning]
end

#supports_lateral_subqueries? ⇒ Boolean

PostgreSQL 9.3rc1+ supports lateral subqueries

Returns:

  • (Boolean) 


1581
1582
1583
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1581

def supports_lateral_subqueries?
  server_version >= 90300
end

#supports_modifying_joins? ⇒ Boolean

PostgreSQL supports modifying joined datasets

Returns:

  • (Boolean) 


1586
1587
1588
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1586

def supports_modifying_joins?
  true
end

#supports_regexp? ⇒ Boolean

PostgreSQL supports pattern matching via regular expressions

Returns:

  • (Boolean) 


1596
1597
1598
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1596

def supports_regexp?
  true
end

#supports_returning?(type) ⇒ Boolean

Returning is always supported.

Returns:

  • (Boolean) 


1591
1592
1593
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1591

def supports_returning?(type)
  true
end

#supports_skip_locked? ⇒ Boolean

PostgreSQL 9.5+ supports SKIP LOCKED.

Returns:

  • (Boolean) 


1601
1602
1603
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1601

def supports_skip_locked?
  server_version >= 90500
end

#supports_timestamp_timezones? ⇒ Boolean

PostgreSQL supports timezones in literal timestamps

Returns:

  • (Boolean) 


1606
1607
1608
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1606

def supports_timestamp_timezones?
  true
end

#supports_window_functions? ⇒ Boolean

PostgreSQL 8.4+ supports window functions

Returns:

  • (Boolean) 


1611
1612
1613
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1611

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



1630
1631
1632
1633
1634
1635
1636
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1630

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. See SQL::Window for a list of options that can be passed in.



1641
1642
1643
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1641

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