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
1367 def analyze
1368   explain(:analyze=>true)
1369 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
1374 def complex_expression_sql_append(sql, op, args)
1375   case op
1376   when :^
1377     j = ' # '
1378     c = false
1379     args.each do |a|
1380       sql << j if c
1381       literal_append(sql, a)
1382       c ||= true
1383     end
1384   when :ILIKE, :'NOT ILIKE'
1385     sql << '('
1386     literal_append(sql, args[0])
1387     sql << ' ' << op.to_s << ' '
1388     literal_append(sql, args[1])
1389     sql << " ESCAPE "
1390     literal_append(sql, "\\")
1391     sql << ')'
1392   else
1393     super
1394   end
1395 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
1411 def disable_insert_returning
1412   clone(:disable_insert_returning=>true)
1413 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
1416 def explain(opts=OPTS)
1417   with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map(:'QUERY PLAN').join("\r\n")
1418 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
1421 def for_share
1422   lock_style(:share)
1423 end
insert(*values) click to toggle source

Insert given values into the database.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1484 def insert(*values)
1485   if @opts[:returning]
1486     # Already know which columns to return, let the standard code handle it
1487     super
1488   elsif @opts[:sql] || @opts[:disable_insert_returning]
1489     # Raw SQL used or RETURNING disabled, just use the default behavior
1490     # and return nil since sequence is not known.
1491     super
1492     nil
1493   else
1494     # Force the use of RETURNING with the primary key value,
1495     # unless it has been disabled.
1496     returning(insert_pk).insert(*values){|r| return r.values.first}
1497   end
1498 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
1535 def insert_conflict(opts=OPTS)
1536   clone(:insert_conflict => opts)
1537 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
1545 def insert_ignore
1546   insert_conflict
1547 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
1552 def insert_select(*values)
1553   return unless supports_insert_select?
1554   # Handle case where query does not return a row
1555   server?(:default).with_sql_first(insert_select_sql(*values)) || false
1556 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
1560 def insert_select_sql(*values)
1561   ds = opts[:returning] ? self : returning
1562   ds.insert_sql(*values)
1563 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
1570 def lock(mode, opts=OPTS)
1571   if block_given? # perform locking inside a transaction and yield to block
1572     @db.transaction(opts){lock(mode, opts); yield}
1573   else
1574     sql = 'LOCK TABLE '.dup
1575     source_list_append(sql, @opts[:from])
1576     mode = mode.to_s.upcase.strip
1577     unless LOCK_MODES.include?(mode)
1578       raise Error, "Unsupported lock mode: #{mode}"
1579     end
1580     sql << " IN #{mode} MODE"
1581     @db.execute(sql, opts)
1582   end
1583   nil
1584 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
1589 def overriding_system_value
1590   clone(:override=>:system)
1591 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
1595 def overriding_user_value
1596   clone(:override=>:user)
1597 end
supports_cte?(type=:select) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1599 def supports_cte?(type=:select)
1600   if type == :select
1601     server_version >= 80400
1602   else
1603     server_version >= 90100
1604   end
1605 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
1609 def supports_cte_in_subqueries?
1610   supports_cte?
1611 end
supports_distinct_on?() click to toggle source

DISTINCT ON is a PostgreSQL extension

     # File lib/sequel/adapters/shared/postgres.rb
1614 def supports_distinct_on?
1615   true
1616 end
supports_group_cube?() click to toggle source

PostgreSQL 9.5+ supports GROUP CUBE

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

PostgreSQL 9.5+ supports GROUP ROLLUP

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

PostgreSQL 9.5+ supports GROUPING SETS

     # File lib/sequel/adapters/shared/postgres.rb
1629 def supports_grouping_sets?
1630   server_version >= 90500
1631 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
1639 def supports_insert_conflict?
1640   server_version >= 90500
1641 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
1634 def supports_insert_select?
1635   !@opts[:disable_insert_returning]
1636 end
supports_lateral_subqueries?() click to toggle source

PostgreSQL 9.3+ supports lateral subqueries

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

PostgreSQL supports modifying joined datasets

     # File lib/sequel/adapters/shared/postgres.rb
1649 def supports_modifying_joins?
1650   true
1651 end
supports_nowait?() click to toggle source

PostgreSQL supports NOWAIT.

     # File lib/sequel/adapters/shared/postgres.rb
1654 def supports_nowait?
1655   true
1656 end
supports_regexp?() click to toggle source

PostgreSQL supports pattern matching via regular expressions

     # File lib/sequel/adapters/shared/postgres.rb
1664 def supports_regexp?
1665   true
1666 end
supports_returning?(type) click to toggle source

Returning is always supported.

     # File lib/sequel/adapters/shared/postgres.rb
1659 def supports_returning?(type)
1660   true
1661 end
supports_skip_locked?() click to toggle source

PostgreSQL 9.5+ supports SKIP LOCKED.

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

PostgreSQL supports timezones in literal timestamps

     # File lib/sequel/adapters/shared/postgres.rb
1674 def supports_timestamp_timezones?
1675   true
1676 end
supports_window_clause?() click to toggle source

PostgreSQL 8.4+ supports WINDOW clause.

     # File lib/sequel/adapters/shared/postgres.rb
1679 def supports_window_clause?
1680   server_version >= 80400
1681 end
supports_window_function_frame_option?(option) click to toggle source

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

     # File lib/sequel/adapters/shared/postgres.rb
1690 def supports_window_function_frame_option?(option)
1691   case option
1692   when :rows, :range
1693     true
1694   when :offset
1695     server_version >= 90000
1696   when :groups, :exclude
1697     server_version >= 110000
1698   end
1699 end
supports_window_functions?() click to toggle source

PostgreSQL 8.4+ supports window functions

     # File lib/sequel/adapters/shared/postgres.rb
1684 def supports_window_functions?
1685   server_version >= 80400
1686 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
1717 def truncate(opts = OPTS)
1718   if opts.empty?
1719     super()
1720   else
1721     clone(:truncate_opts=>opts).truncate
1722   end
1723 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
1731 def _import(columns, values, opts=OPTS)
1732   if @opts[:returning]
1733     statements = multi_insert_sql(columns, values)
1734     trans_opts = Hash[opts]
1735     trans_opts[:server] = @opts[:server]
1736     @db.transaction(trans_opts) do
1737       statements.map{|st| returning_fetch_rows(st)}
1738     end.first.map{|v| v.length == 1 ? v.values.first : v}
1739   elsif opts[:return] == :primary_key
1740     returning(insert_pk)._import(columns, values, opts)
1741   else
1742     super
1743   end
1744 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
1749 def _truncate_sql(table)
1750   to = @opts[:truncate_opts] || OPTS
1751   "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}"
1752 end
check_truncation_allowed!() click to toggle source

Allow truncation of multiple source tables.

     # File lib/sequel/adapters/shared/postgres.rb
1755 def check_truncation_allowed!
1756   raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group]
1757   raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join]
1758 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
1881 def compound_dataset_sql_append(sql, ds)
1882   sql << '('
1883   super
1884   sql << ')'
1885 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
1761 def delete_from_sql(sql)
1762   sql << ' FROM '
1763   source_list_append(sql, @opts[:from][0..0])
1764 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
1767 def delete_using_sql(sql)
1768   join_from_sql(:USING, sql)
1769 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
1942 def full_text_string_join(cols)
1943   cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')}
1944   cols = cols.zip([' '] * cols.length).flatten
1945   cols.pop
1946   SQL::StringExpression.new(:'||', *cols)
1947 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
1772 def insert_conflict_sql(sql)
1773   if opts = @opts[:insert_conflict]
1774     sql << " ON CONFLICT"
1775 
1776     if target = opts[:constraint] 
1777       sql << " ON CONSTRAINT "
1778       identifier_append(sql, target)
1779     elsif target = opts[:target]
1780       sql << ' '
1781       identifier_append(sql, Array(target))
1782       if conflict_where = opts[:conflict_where]
1783         sql << " WHERE "
1784         literal_append(sql, conflict_where)
1785       end
1786     end
1787 
1788     if values = opts[:update]
1789       sql << " DO UPDATE SET "
1790       update_sql_values_hash(sql, values)
1791       if update_where = opts[:update_where]
1792         sql << " WHERE "
1793         literal_append(sql, update_where)
1794       end
1795     else
1796       sql << " DO NOTHING"
1797     end
1798   end
1799 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
1802 def insert_pk
1803   if (f = opts[:from]) && !f.empty?
1804     case t = f.first
1805     when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier
1806       if pk = db.primary_key(t)
1807         Sequel::SQL::Identifier.new(pk)
1808       end
1809     end
1810   end
1811 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
1814 def insert_values_sql(sql)
1815   case opts[:override]
1816   when :system
1817     sql << " OVERRIDING SYSTEM VALUE"
1818   when :user
1819     sql << " OVERRIDING USER VALUE"
1820   end
1821   super
1822 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
1826 def join_from_sql(type, sql)
1827   if(from = @opts[:from][1..-1]).empty?
1828     raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join]
1829   else
1830     sql << ' ' << type.to_s << ' '
1831     source_list_append(sql, from)
1832     select_join_sql(sql)
1833   end
1834 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
1837 def literal_blob_append(sql, v)
1838   sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'"
1839 end
literal_false() click to toggle source

PostgreSQL uses FALSE for false values

     # File lib/sequel/adapters/shared/postgres.rb
1842 def literal_false
1843   'false'
1844 end
literal_float(value) click to toggle source

PostgreSQL quotes NaN and Infinity.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1847 def literal_float(value)
1848   if value.finite?
1849     super
1850   elsif value.nan?
1851     "'NaN'"
1852   elsif value.infinite? == 1
1853     "'Infinity'"
1854   else
1855     "'-Infinity'"
1856   end
1857 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
1860 def literal_string_append(sql, v)
1861   sql << "'" << v.gsub("'", "''") << "'"
1862 end
literal_true() click to toggle source

PostgreSQL uses true for true values

     # File lib/sequel/adapters/shared/postgres.rb
1865 def literal_true
1866   'true'
1867 end
multi_insert_sql_strategy() click to toggle source

PostgreSQL supports multiple rows in INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
1870 def multi_insert_sql_strategy
1871   :values
1872 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
1875 def non_sql_option?(key)
1876   super || key == :cursor || key == :insert_conflict
1877 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
1889 def requires_like_escape?
1890   false
1891 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
1895 def select_lock_sql(sql)
1896   lock = @opts[:lock]
1897   if lock == :share
1898     sql << ' FOR SHARE'
1899   else
1900     super
1901   end
1902 
1903   if lock
1904     if @opts[:skip_locked]
1905       sql << " SKIP LOCKED"
1906     elsif @opts[:nowait]
1907       sql << " NOWAIT"
1908     end
1909   end
1910 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
1913 def select_values_sql(sql)
1914   sql << "VALUES "
1915   expression_list_append(sql, opts[:values])
1916 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
1919 def select_with_sql_base
1920   opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
1921 end
server_version() click to toggle source

The version of the database server

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

PostgreSQL supports quoted function names.

     # File lib/sequel/adapters/shared/postgres.rb
1929 def supports_quoted_function_names?
1930   true
1931 end
to_prepared_statement(type, *a) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1933 def to_prepared_statement(type, *a)
1934   if type == :insert && !@opts.has_key?(:returning)
1935     returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a)
1936   else
1937     super
1938   end
1939 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
1950 def update_from_sql(sql)
1951   join_from_sql(:FROM, sql)
1952 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
1955 def update_table_sql(sql)
1956   sql << ' '
1957   source_list_append(sql, @opts[:from][0..0])
1958 end