Module: Sequel::Postgres::DatasetMethods

Includes:
UnmodifiedIdentifiers::DatasetMethods
Included in:
JDBC::Postgres::Dataset, Dataset
Defined in:
lib/sequel/adapters/shared/postgres.rb

Constant Summary collapse

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

Instance Method Summary collapse

Instance Method Details

#analyzeObject

Return the results of an EXPLAIN ANALYZE query as a string


1530
1531
1532
# File 'lib/sequel/adapters/shared/postgres.rb', line 1530

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.


1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
# File 'lib/sequel/adapters/shared/postgres.rb', line 1537

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_returningObject

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


1574
1575
1576
# File 'lib/sequel/adapters/shared/postgres.rb', line 1574

def disable_insert_returning
  clone(:disable_insert_returning=>true)
end

#explain(opts = OPTS) ⇒ Object

Return the results of an EXPLAIN query as a string


1579
1580
1581
# File 'lib/sequel/adapters/shared/postgres.rb', line 1579

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

#for_shareObject

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


1584
1585
1586
# File 'lib/sequel/adapters/shared/postgres.rb', line 1584

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.


1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
# File 'lib/sequel/adapters/shared/postgres.rb', line 1607

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.


1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
# File 'lib/sequel/adapters/shared/postgres.rb', line 1647

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: Sequel[: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: Sequel[:excluded][:b]}, update_where: {Sequel[: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)

1698
1699
1700
# File 'lib/sequel/adapters/shared/postgres.rb', line 1698

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

#insert_ignoreObject

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

1708
1709
1710
# File 'lib/sequel/adapters/shared/postgres.rb', line 1708

def insert_ignore
  insert_conflict
end

#insert_select(*values) ⇒ Object

Insert a record, returning the record inserted, using RETURNING. Always returns nil without running an INSERT statement if disable_insert_returning is used. If the query runs but returns no values, returns false.


1715
1716
1717
1718
1719
# File 'lib/sequel/adapters/shared/postgres.rb', line 1715

def insert_select(*values)
  return unless supports_insert_select?
  # Handle case where query does not return a row
  server?(:default).with_sql_first(insert_select_sql(*values)) || false
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.


1723
1724
1725
1726
# File 'lib/sequel/adapters/shared/postgres.rb', line 1723

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.


1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
# File 'lib/sequel/adapters/shared/postgres.rb', line 1733

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

#overriding_system_valueObject

Use OVERRIDING USER VALUE for INSERT statements, so that identity columns always use the user supplied value, and an error is not raised for identity columns that are GENERATED ALWAYS.


1752
1753
1754
# File 'lib/sequel/adapters/shared/postgres.rb', line 1752

def overriding_system_value
  clone(:override=>:system)
end

#overriding_user_valueObject

Use OVERRIDING USER VALUE for INSERT statements, so that identity columns always use the sequence value instead of the user supplied value.


1758
1759
1760
# File 'lib/sequel/adapters/shared/postgres.rb', line 1758

def overriding_user_value
  clone(:override=>:user)
end

#supports_cte?(type = :select) ⇒ Boolean

Returns:

  • (Boolean)

1762
1763
1764
1765
1766
1767
1768
# File 'lib/sequel/adapters/shared/postgres.rb', line 1762

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)

1772
1773
1774
# File 'lib/sequel/adapters/shared/postgres.rb', line 1772

def supports_cte_in_subqueries?
  supports_cte?
end

#supports_distinct_on?Boolean

DISTINCT ON is a PostgreSQL extension

Returns:

  • (Boolean)

1777
1778
1779
# File 'lib/sequel/adapters/shared/postgres.rb', line 1777

def supports_distinct_on?
  true
end

#supports_group_cube?Boolean

PostgreSQL 9.5+ supports GROUP CUBE

Returns:

  • (Boolean)

1782
1783
1784
# File 'lib/sequel/adapters/shared/postgres.rb', line 1782

def supports_group_cube?
  server_version >= 90500
end

#supports_group_rollup?Boolean

PostgreSQL 9.5+ supports GROUP ROLLUP

Returns:

  • (Boolean)

1787
1788
1789
# File 'lib/sequel/adapters/shared/postgres.rb', line 1787

def supports_group_rollup?
  server_version >= 90500
end

#supports_grouping_sets?Boolean

PostgreSQL 9.5+ supports GROUPING SETS

Returns:

  • (Boolean)

1792
1793
1794
# File 'lib/sequel/adapters/shared/postgres.rb', line 1792

def supports_grouping_sets?
  server_version >= 90500
end

#supports_insert_conflict?Boolean

PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.

Returns:

  • (Boolean)

1802
1803
1804
# File 'lib/sequel/adapters/shared/postgres.rb', line 1802

def supports_insert_conflict?
  server_version >= 90500
end

#supports_insert_select?Boolean

True unless insert returning has been disabled for this dataset.

Returns:

  • (Boolean)

1797
1798
1799
# File 'lib/sequel/adapters/shared/postgres.rb', line 1797

def supports_insert_select?
  !@opts[:disable_insert_returning]
end

#supports_lateral_subqueries?Boolean

PostgreSQL 9.3+ supports lateral subqueries

Returns:

  • (Boolean)

1807
1808
1809
# File 'lib/sequel/adapters/shared/postgres.rb', line 1807

def supports_lateral_subqueries?
  server_version >= 90300
end

#supports_modifying_joins?Boolean

PostgreSQL supports modifying joined datasets

Returns:

  • (Boolean)

1812
1813
1814
# File 'lib/sequel/adapters/shared/postgres.rb', line 1812

def supports_modifying_joins?
  true
end

#supports_nowait?Boolean

PostgreSQL supports NOWAIT.

Returns:

  • (Boolean)

1817
1818
1819
# File 'lib/sequel/adapters/shared/postgres.rb', line 1817

def supports_nowait?
  true
end

#supports_regexp?Boolean

PostgreSQL supports pattern matching via regular expressions

Returns:

  • (Boolean)

1827
1828
1829
# File 'lib/sequel/adapters/shared/postgres.rb', line 1827

def supports_regexp?
  true
end

#supports_returning?(type) ⇒ Boolean

Returning is always supported.

Returns:

  • (Boolean)

1822
1823
1824
# File 'lib/sequel/adapters/shared/postgres.rb', line 1822

def supports_returning?(type)
  true
end

#supports_skip_locked?Boolean

PostgreSQL 9.5+ supports SKIP LOCKED.

Returns:

  • (Boolean)

1832
1833
1834
# File 'lib/sequel/adapters/shared/postgres.rb', line 1832

def supports_skip_locked?
  server_version >= 90500
end

#supports_timestamp_timezones?Boolean

PostgreSQL supports timezones in literal timestamps

Returns:

  • (Boolean)

1837
1838
1839
# File 'lib/sequel/adapters/shared/postgres.rb', line 1837

def supports_timestamp_timezones?
  true
end

#supports_window_clause?Boolean

PostgreSQL 8.4+ supports WINDOW clause.

Returns:

  • (Boolean)

1842
1843
1844
# File 'lib/sequel/adapters/shared/postgres.rb', line 1842

def supports_window_clause?
  server_version >= 80400
end

#supports_window_function_frame_option?(option) ⇒ Boolean

Base support added in 8.4, offset supported added in 9.0, GROUPS and EXCLUDE support added in 11.0.

Returns:

  • (Boolean)

1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
# File 'lib/sequel/adapters/shared/postgres.rb', line 1853

def supports_window_function_frame_option?(option)
  case option
  when :rows, :range
    true
  when :offset
    server_version >= 90000
  when :groups, :exclude
    server_version >= 110000
  end
end

#supports_window_functions?Boolean

PostgreSQL 8.4+ supports window functions

Returns:

  • (Boolean)

1847
1848
1849
# File 'lib/sequel/adapters/shared/postgres.rb', line 1847

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"

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

1880
1881
1882
1883
1884
1885
1886
# File 'lib/sequel/adapters/shared/postgres.rb', line 1880

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

#with_tiesObject

Use WITH TIES when limiting the result set to also include additional rules that have the same results for the order column as the final row. Requires PostgreSQL 13.


1891
1892
1893
# File 'lib/sequel/adapters/shared/postgres.rb', line 1891

def with_ties
  clone(:limit_with_ties=>true)
end