module Sequel::Postgres::DatasetMethods

Constants

LOCK_MODES
NULL

Public Instance Methods

analyze() click to toggle source

Return the results of an EXPLAIN ANALYZE query as a string

     # File lib/sequel/adapters/shared/postgres.rb
1355 def analyze
1356   explain(:analyze=>true)
1357 end
complex_expression_sql_append(sql, op, args) click to toggle source

Handle converting the ruby xor operator (^) into the PostgreSQL xor operator (#), and use the ILIKE and NOT ILIKE operators.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1362 def complex_expression_sql_append(sql, op, args)
1363   case op
1364   when :^
1365     j = ' # '
1366     c = false
1367     args.each do |a|
1368       sql << j if c
1369       literal_append(sql, a)
1370       c ||= true
1371     end
1372   when :ILIKE, :'NOT ILIKE'
1373     sql << '('
1374     literal_append(sql, args[0])
1375     sql << ' ' << op.to_s << ' '
1376     literal_append(sql, args[1])
1377     sql << " ESCAPE "
1378     literal_append(sql, "\\")
1379     sql << ')'
1380   else
1381     super
1382   end
1383 end
disable_insert_returning() click to toggle source

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

     # File lib/sequel/adapters/shared/postgres.rb
1399 def disable_insert_returning
1400   clone(:disable_insert_returning=>true)
1401 end
explain(opts=OPTS) click to toggle source

Return the results of an EXPLAIN query as a string

     # File lib/sequel/adapters/shared/postgres.rb
1404 def explain(opts=OPTS)
1405   with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map(:'QUERY PLAN').join("\r\n")
1406 end
for_share() click to toggle source

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

     # File lib/sequel/adapters/shared/postgres.rb
1409 def for_share
1410   lock_style(:share)
1411 end
insert(*values) click to toggle source

Insert given values into the database.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1472 def insert(*values)
1473   if @opts[:returning]
1474     # Already know which columns to return, let the standard code handle it
1475     super
1476   elsif @opts[:sql] || @opts[:disable_insert_returning]
1477     # Raw SQL used or RETURNING disabled, just use the default behavior
1478     # and return nil since sequence is not known.
1479     super
1480     nil
1481   else
1482     # Force the use of RETURNING with the primary key value,
1483     # unless it has been disabled.
1484     returning(insert_pk).insert(*values){|r| return r.values.first}
1485   end
1486 end
insert_conflict(opts=OPTS) click to toggle source

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)
     # File lib/sequel/adapters/shared/postgres.rb
1523 def insert_conflict(opts=OPTS)
1524   clone(:insert_conflict => opts)
1525 end
insert_ignore() click to toggle source

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
     # File lib/sequel/adapters/shared/postgres.rb
1533 def insert_ignore
1534   insert_conflict
1535 end
insert_select(*values) click to toggle source

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.

     # File lib/sequel/adapters/shared/postgres.rb
1540 def insert_select(*values)
1541   return unless supports_insert_select?
1542   # Handle case where query does not return a row
1543   server?(:default).with_sql_first(insert_select_sql(*values)) || false
1544 end
insert_select_sql(*values) click to toggle source

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

     # File lib/sequel/adapters/shared/postgres.rb
1548 def insert_select_sql(*values)
1549   ds = opts[:returning] ? self : returning
1550   ds.insert_sql(*values)
1551 end
lock(mode, opts=OPTS) { || ... } click to toggle source

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.

     # File lib/sequel/adapters/shared/postgres.rb
1558 def lock(mode, opts=OPTS)
1559   if block_given? # perform locking inside a transaction and yield to block
1560     @db.transaction(opts){lock(mode, opts); yield}
1561   else
1562     sql = 'LOCK TABLE '.dup
1563     source_list_append(sql, @opts[:from])
1564     mode = mode.to_s.upcase.strip
1565     unless LOCK_MODES.include?(mode)
1566       raise Error, "Unsupported lock mode: #{mode}"
1567     end
1568     sql << " IN #{mode} MODE"
1569     @db.execute(sql, opts)
1570   end
1571   nil
1572 end
overriding_system_value() click to toggle source

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.

     # File lib/sequel/adapters/shared/postgres.rb
1577 def overriding_system_value
1578   clone(:override=>:system)
1579 end
overriding_user_value() click to toggle source

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

     # File lib/sequel/adapters/shared/postgres.rb
1583 def overriding_user_value
1584   clone(:override=>:user)
1585 end
supports_cte?(type=:select) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1587 def supports_cte?(type=:select)
1588   if type == :select
1589     server_version >= 80400
1590   else
1591     server_version >= 90100
1592   end
1593 end
supports_cte_in_subqueries?() click to toggle source

PostgreSQL supports using the WITH clause in subqueries if it supports using WITH at all (i.e. on PostgreSQL 8.4+).

     # File lib/sequel/adapters/shared/postgres.rb
1597 def supports_cte_in_subqueries?
1598   supports_cte?
1599 end
supports_distinct_on?() click to toggle source

DISTINCT ON is a PostgreSQL extension

     # File lib/sequel/adapters/shared/postgres.rb
1602 def supports_distinct_on?
1603   true
1604 end
supports_group_cube?() click to toggle source

PostgreSQL 9.5+ supports GROUP CUBE

     # File lib/sequel/adapters/shared/postgres.rb
1607 def supports_group_cube?
1608   server_version >= 90500
1609 end
supports_group_rollup?() click to toggle source

PostgreSQL 9.5+ supports GROUP ROLLUP

     # File lib/sequel/adapters/shared/postgres.rb
1612 def supports_group_rollup?
1613   server_version >= 90500
1614 end
supports_grouping_sets?() click to toggle source

PostgreSQL 9.5+ supports GROUPING SETS

     # File lib/sequel/adapters/shared/postgres.rb
1617 def supports_grouping_sets?
1618   server_version >= 90500
1619 end
supports_insert_conflict?() click to toggle source

PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
1627 def supports_insert_conflict?
1628   server_version >= 90500
1629 end
supports_insert_select?() click to toggle source

True unless insert returning has been disabled for this dataset.

     # File lib/sequel/adapters/shared/postgres.rb
1622 def supports_insert_select?
1623   !@opts[:disable_insert_returning]
1624 end
supports_lateral_subqueries?() click to toggle source

PostgreSQL 9.3+ supports lateral subqueries

     # File lib/sequel/adapters/shared/postgres.rb
1632 def supports_lateral_subqueries?
1633   server_version >= 90300
1634 end
supports_modifying_joins?() click to toggle source

PostgreSQL supports modifying joined datasets

     # File lib/sequel/adapters/shared/postgres.rb
1637 def supports_modifying_joins?
1638   true
1639 end
supports_nowait?() click to toggle source

PostgreSQL supports NOWAIT.

     # File lib/sequel/adapters/shared/postgres.rb
1642 def supports_nowait?
1643   true
1644 end
supports_regexp?() click to toggle source

PostgreSQL supports pattern matching via regular expressions

     # File lib/sequel/adapters/shared/postgres.rb
1652 def supports_regexp?
1653   true
1654 end
supports_returning?(type) click to toggle source

Returning is always supported.

     # File lib/sequel/adapters/shared/postgres.rb
1647 def supports_returning?(type)
1648   true
1649 end
supports_skip_locked?() click to toggle source

PostgreSQL 9.5+ supports SKIP LOCKED.

     # File lib/sequel/adapters/shared/postgres.rb
1657 def supports_skip_locked?
1658   server_version >= 90500
1659 end
supports_timestamp_timezones?() click to toggle source

PostgreSQL supports timezones in literal timestamps

     # File lib/sequel/adapters/shared/postgres.rb
1662 def supports_timestamp_timezones?
1663   true
1664 end
supports_window_functions?() click to toggle source

PostgreSQL 8.4+ supports window functions

     # File lib/sequel/adapters/shared/postgres.rb
1667 def supports_window_functions?
1668   server_version >= 80400
1669 end
truncate(opts = OPTS) click to toggle source

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
Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1687 def truncate(opts = OPTS)
1688   if opts.empty?
1689     super()
1690   else
1691     clone(:truncate_opts=>opts).truncate
1692   end
1693 end
window(name, opts) click to toggle source

Return a clone of the dataset with an addition named window that can be referenced in window functions. See Sequel::SQL::Window for a list of options that can be passed in.

     # File lib/sequel/adapters/shared/postgres.rb
1698 def window(name, opts)
1699   clone(:window=>(@opts[:window]||[]) + [[name, SQL::Window.new(opts)]])
1700 end

Protected Instance Methods

_import(columns, values, opts=OPTS) click to toggle source

If returned primary keys are requested, use RETURNING unless already set on the dataset. If RETURNING is already set, use existing returning values. If RETURNING is only set to return a single columns, return an array of just that column. Otherwise, return an array of hashes.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1708 def _import(columns, values, opts=OPTS)
1709   if @opts[:returning]
1710     statements = multi_insert_sql(columns, values)
1711     @db.transaction(Hash[opts].merge!(:server=>@opts[:server])) do
1712       statements.map{|st| returning_fetch_rows(st)}
1713     end.first.map{|v| v.length == 1 ? v.values.first : v}
1714   elsif opts[:return] == :primary_key
1715     returning(insert_pk)._import(columns, values, opts)
1716   else
1717     super
1718   end
1719 end

Private Instance Methods

_truncate_sql(table) click to toggle source

Format TRUNCATE statement with PostgreSQL specific options.

     # File lib/sequel/adapters/shared/postgres.rb
1724 def _truncate_sql(table)
1725   to = @opts[:truncate_opts] || OPTS
1726   "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}"
1727 end
check_truncation_allowed!() click to toggle source

Allow truncation of multiple source tables.

     # File lib/sequel/adapters/shared/postgres.rb
1730 def check_truncation_allowed!
1731   raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group]
1732   raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join]
1733 end
compound_dataset_sql_append(sql, ds) click to toggle source

PostgreSQL requires parentheses around compound datasets if they use CTEs, and using them in other places doesn't hurt.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1856 def compound_dataset_sql_append(sql, ds)
1857   sql << '('
1858   super
1859   sql << ')'
1860 end
delete_from_sql(sql) click to toggle source

Only include the primary table in the main delete clause

     # File lib/sequel/adapters/shared/postgres.rb
1736 def delete_from_sql(sql)
1737   sql << ' FROM '
1738   source_list_append(sql, @opts[:from][0..0])
1739 end
delete_using_sql(sql) click to toggle source

Use USING to specify additional tables in a delete query

     # File lib/sequel/adapters/shared/postgres.rb
1742 def delete_using_sql(sql)
1743   join_from_sql(:USING, sql)
1744 end
full_text_string_join(cols) click to toggle source

Concatenate the expressions with a space in between

     # File lib/sequel/adapters/shared/postgres.rb
1934 def full_text_string_join(cols)
1935   cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')}
1936   cols = cols.zip([' '] * cols.length).flatten
1937   cols.pop
1938   SQL::StringExpression.new(:'||', *cols)
1939 end
insert_conflict_sql(sql) click to toggle source

Add ON CONFLICT clause if it should be used

     # File lib/sequel/adapters/shared/postgres.rb
1747 def insert_conflict_sql(sql)
1748   if opts = @opts[:insert_conflict]
1749     sql << " ON CONFLICT"
1750 
1751     if target = opts[:constraint] 
1752       sql << " ON CONSTRAINT "
1753       identifier_append(sql, target)
1754     elsif target = opts[:target]
1755       sql << ' '
1756       identifier_append(sql, Array(target))
1757       if conflict_where = opts[:conflict_where]
1758         sql << " WHERE "
1759         literal_append(sql, conflict_where)
1760       end
1761     end
1762 
1763     if values = opts[:update]
1764       sql << " DO UPDATE SET "
1765       update_sql_values_hash(sql, values)
1766       if update_where = opts[:update_where]
1767         sql << " WHERE "
1768         literal_append(sql, update_where)
1769       end
1770     else
1771       sql << " DO NOTHING"
1772     end
1773   end
1774 end
insert_pk() click to toggle source

Return the primary key to use for RETURNING in an INSERT statement

     # File lib/sequel/adapters/shared/postgres.rb
1777 def insert_pk
1778   if (f = opts[:from]) && !f.empty?
1779     case t = f.first
1780     when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier
1781       if pk = db.primary_key(t)
1782         Sequel::SQL::Identifier.new(pk)
1783       end
1784     end
1785   end
1786 end
insert_values_sql(sql) click to toggle source

Support OVERRIDING SYSTEM|USER VALUE in insert statements

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1789 def insert_values_sql(sql)
1790   case opts[:override]
1791   when :system
1792     sql << " OVERRIDING SYSTEM VALUE"
1793   when :user
1794     sql << " OVERRIDING USER VALUE"
1795   end
1796   super
1797 end
join_from_sql(type, sql) click to toggle source

For multiple table support, PostgreSQL requires at least two from tables, with joins allowed.

     # File lib/sequel/adapters/shared/postgres.rb
1801 def join_from_sql(type, sql)
1802   if(from = @opts[:from][1..-1]).empty?
1803     raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join]
1804   else
1805     sql << ' ' << type.to_s << ' '
1806     source_list_append(sql, from)
1807     select_join_sql(sql)
1808   end
1809 end
literal_blob_append(sql, v) click to toggle source

Use a generic blob quoting method, hopefully overridden in one of the subadapter methods

     # File lib/sequel/adapters/shared/postgres.rb
1812 def literal_blob_append(sql, v)
1813   sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'"
1814 end
literal_false() click to toggle source

PostgreSQL uses FALSE for false values

     # File lib/sequel/adapters/shared/postgres.rb
1817 def literal_false
1818   'false'
1819 end
literal_float(value) click to toggle source

PostgreSQL quotes NaN and Infinity.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1822 def literal_float(value)
1823   if value.finite?
1824     super
1825   elsif value.nan?
1826     "'NaN'"
1827   elsif value.infinite? == 1
1828     "'Infinity'"
1829   else
1830     "'-Infinity'"
1831   end
1832 end
literal_string_append(sql, v) click to toggle source

Assume that SQL standard quoting is on, per Sequel's defaults

     # File lib/sequel/adapters/shared/postgres.rb
1835 def literal_string_append(sql, v)
1836   sql << "'" << v.gsub("'", "''") << "'"
1837 end
literal_true() click to toggle source

PostgreSQL uses true for true values

     # File lib/sequel/adapters/shared/postgres.rb
1840 def literal_true
1841   'true'
1842 end
multi_insert_sql_strategy() click to toggle source

PostgreSQL supports multiple rows in INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
1845 def multi_insert_sql_strategy
1846   :values
1847 end
non_sql_option?(key) click to toggle source

Dataset options that do not affect the generated SQL.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1850 def non_sql_option?(key)
1851   super || key == :cursor || key == :insert_conflict
1852 end
requires_like_escape?() click to toggle source

Backslash is supported by default as the escape character on PostgreSQL, and using ESCAPE can break LIKE ANY() usage.

     # File lib/sequel/adapters/shared/postgres.rb
1864 def requires_like_escape?
1865   false
1866 end
select_lock_sql(sql) click to toggle source

Support FOR SHARE locking when using the :share lock style. Use SKIP LOCKED if skipping locked rows.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1870 def select_lock_sql(sql)
1871   lock = @opts[:lock]
1872   if lock == :share
1873     sql << ' FOR SHARE'
1874   else
1875     super
1876   end
1877 
1878   if lock
1879     if @opts[:skip_locked]
1880       sql << " SKIP LOCKED"
1881     elsif @opts[:nowait]
1882       sql << " NOWAIT"
1883     end
1884   end
1885 end
select_values_sql(sql) click to toggle source

Support VALUES clause instead of the SELECT clause to return rows.

     # File lib/sequel/adapters/shared/postgres.rb
1888 def select_values_sql(sql)
1889   sql << "VALUES "
1890   expression_list_append(sql, opts[:values])
1891 end
select_window_sql(sql) click to toggle source

SQL fragment for named window specifications

     # File lib/sequel/adapters/shared/postgres.rb
1894 def select_window_sql(sql)
1895   if ws = @opts[:window]
1896     sql << " WINDOW "
1897     c = false
1898     co = ', '
1899     as = ' AS '
1900     ws.map do |name, window|
1901       sql << co if c
1902       literal_append(sql, name)
1903       sql << as
1904       literal_append(sql, window)
1905       c ||= true
1906     end
1907   end
1908 end
select_with_sql_base() click to toggle source

Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1911 def select_with_sql_base
1912   opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
1913 end
server_version() click to toggle source

The version of the database server

     # File lib/sequel/adapters/shared/postgres.rb
1916 def server_version
1917   db.server_version(@opts[:server])
1918 end
supports_quoted_function_names?() click to toggle source

PostgreSQL supports quoted function names.

     # File lib/sequel/adapters/shared/postgres.rb
1921 def supports_quoted_function_names?
1922   true
1923 end
to_prepared_statement(type, *a) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1925 def to_prepared_statement(type, *a)
1926   if type == :insert && !@opts.has_key?(:returning)
1927     returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a)
1928   else
1929     super
1930   end
1931 end
update_from_sql(sql) click to toggle source

Use FROM to specify additional tables in an update query

     # File lib/sequel/adapters/shared/postgres.rb
1942 def update_from_sql(sql)
1943   join_from_sql(:FROM, sql)
1944 end
update_table_sql(sql) click to toggle source

Only include the primary table in the main update clause

     # File lib/sequel/adapters/shared/postgres.rb
1947 def update_table_sql(sql)
1948   sql << ' '
1949   source_list_append(sql, @opts[:from][0..0])
1950 end