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
1375 def analyze
1376   explain(:analyze=>true)
1377 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
1382 def complex_expression_sql_append(sql, op, args)
1383   case op
1384   when :^
1385     j = ' # '
1386     c = false
1387     args.each do |a|
1388       sql << j if c
1389       literal_append(sql, a)
1390       c ||= true
1391     end
1392   when :ILIKE, :'NOT ILIKE'
1393     sql << '('
1394     literal_append(sql, args[0])
1395     sql << ' ' << op.to_s << ' '
1396     literal_append(sql, args[1])
1397     sql << " ESCAPE "
1398     literal_append(sql, "\\")
1399     sql << ')'
1400   else
1401     super
1402   end
1403 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
1419 def disable_insert_returning
1420   clone(:disable_insert_returning=>true)
1421 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
1424 def explain(opts=OPTS)
1425   with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map(:'QUERY PLAN').join("\r\n")
1426 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
1429 def for_share
1430   lock_style(:share)
1431 end
insert(*values) click to toggle source

Insert given values into the database.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1492 def insert(*values)
1493   if @opts[:returning]
1494     # Already know which columns to return, let the standard code handle it
1495     super
1496   elsif @opts[:sql] || @opts[:disable_insert_returning]
1497     # Raw SQL used or RETURNING disabled, just use the default behavior
1498     # and return nil since sequence is not known.
1499     super
1500     nil
1501   else
1502     # Force the use of RETURNING with the primary key value,
1503     # unless it has been disabled.
1504     returning(insert_pk).insert(*values){|r| return r.values.first}
1505   end
1506 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
1543 def insert_conflict(opts=OPTS)
1544   clone(:insert_conflict => opts)
1545 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
1553 def insert_ignore
1554   insert_conflict
1555 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
1560 def insert_select(*values)
1561   return unless supports_insert_select?
1562   # Handle case where query does not return a row
1563   server?(:default).with_sql_first(insert_select_sql(*values)) || false
1564 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
1568 def insert_select_sql(*values)
1569   ds = opts[:returning] ? self : returning
1570   ds.insert_sql(*values)
1571 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
1578 def lock(mode, opts=OPTS)
1579   if block_given? # perform locking inside a transaction and yield to block
1580     @db.transaction(opts){lock(mode, opts); yield}
1581   else
1582     sql = 'LOCK TABLE '.dup
1583     source_list_append(sql, @opts[:from])
1584     mode = mode.to_s.upcase.strip
1585     unless LOCK_MODES.include?(mode)
1586       raise Error, "Unsupported lock mode: #{mode}"
1587     end
1588     sql << " IN #{mode} MODE"
1589     @db.execute(sql, opts)
1590   end
1591   nil
1592 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
1597 def overriding_system_value
1598   clone(:override=>:system)
1599 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
1603 def overriding_user_value
1604   clone(:override=>:user)
1605 end
supports_cte?(type=:select) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1607 def supports_cte?(type=:select)
1608   if type == :select
1609     server_version >= 80400
1610   else
1611     server_version >= 90100
1612   end
1613 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
1617 def supports_cte_in_subqueries?
1618   supports_cte?
1619 end
supports_distinct_on?() click to toggle source

DISTINCT ON is a PostgreSQL extension

     # File lib/sequel/adapters/shared/postgres.rb
1622 def supports_distinct_on?
1623   true
1624 end
supports_group_cube?() click to toggle source

PostgreSQL 9.5+ supports GROUP CUBE

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

PostgreSQL 9.5+ supports GROUP ROLLUP

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

PostgreSQL 9.5+ supports GROUPING SETS

     # File lib/sequel/adapters/shared/postgres.rb
1637 def supports_grouping_sets?
1638   server_version >= 90500
1639 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
1647 def supports_insert_conflict?
1648   server_version >= 90500
1649 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
1642 def supports_insert_select?
1643   !@opts[:disable_insert_returning]
1644 end
supports_lateral_subqueries?() click to toggle source

PostgreSQL 9.3+ supports lateral subqueries

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

PostgreSQL supports modifying joined datasets

     # File lib/sequel/adapters/shared/postgres.rb
1657 def supports_modifying_joins?
1658   true
1659 end
supports_nowait?() click to toggle source

PostgreSQL supports NOWAIT.

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

PostgreSQL supports pattern matching via regular expressions

     # File lib/sequel/adapters/shared/postgres.rb
1672 def supports_regexp?
1673   true
1674 end
supports_returning?(type) click to toggle source

Returning is always supported.

     # File lib/sequel/adapters/shared/postgres.rb
1667 def supports_returning?(type)
1668   true
1669 end
supports_skip_locked?() click to toggle source

PostgreSQL 9.5+ supports SKIP LOCKED.

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

PostgreSQL supports timezones in literal timestamps

     # File lib/sequel/adapters/shared/postgres.rb
1682 def supports_timestamp_timezones?
1683   true
1684 end
supports_window_clause?() click to toggle source

PostgreSQL 8.4+ supports WINDOW clause.

     # File lib/sequel/adapters/shared/postgres.rb
1687 def supports_window_clause?
1688   server_version >= 80400
1689 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
1698 def supports_window_function_frame_option?(option)
1699   case option
1700   when :rows, :range
1701     true
1702   when :offset
1703     server_version >= 90000
1704   when :groups, :exclude
1705     server_version >= 110000
1706   end
1707 end
supports_window_functions?() click to toggle source

PostgreSQL 8.4+ supports window functions

     # File lib/sequel/adapters/shared/postgres.rb
1692 def supports_window_functions?
1693   server_version >= 80400
1694 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
1725 def truncate(opts = OPTS)
1726   if opts.empty?
1727     super()
1728   else
1729     clone(:truncate_opts=>opts).truncate
1730   end
1731 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
1739 def _import(columns, values, opts=OPTS)
1740   if @opts[:returning]
1741     statements = multi_insert_sql(columns, values)
1742     trans_opts = Hash[opts]
1743     trans_opts[:server] = @opts[:server]
1744     @db.transaction(trans_opts) do
1745       statements.map{|st| returning_fetch_rows(st)}
1746     end.first.map{|v| v.length == 1 ? v.values.first : v}
1747   elsif opts[:return] == :primary_key
1748     returning(insert_pk)._import(columns, values, opts)
1749   else
1750     super
1751   end
1752 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
1757 def _truncate_sql(table)
1758   to = @opts[:truncate_opts] || OPTS
1759   "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}"
1760 end
check_truncation_allowed!() click to toggle source

Allow truncation of multiple source tables.

     # File lib/sequel/adapters/shared/postgres.rb
1763 def check_truncation_allowed!
1764   raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group]
1765   raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join]
1766 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
1899 def compound_dataset_sql_append(sql, ds)
1900   sql << '('
1901   super
1902   sql << ')'
1903 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
1769 def delete_from_sql(sql)
1770   sql << ' FROM '
1771   source_list_append(sql, @opts[:from][0..0])
1772 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
1775 def delete_using_sql(sql)
1776   join_from_sql(:USING, sql)
1777 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
1972 def full_text_string_join(cols)
1973   cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')}
1974   cols = cols.zip([' '] * cols.length).flatten
1975   cols.pop
1976   SQL::StringExpression.new(:'||', *cols)
1977 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
1780 def insert_conflict_sql(sql)
1781   if opts = @opts[:insert_conflict]
1782     sql << " ON CONFLICT"
1783 
1784     if target = opts[:constraint] 
1785       sql << " ON CONSTRAINT "
1786       identifier_append(sql, target)
1787     elsif target = opts[:target]
1788       sql << ' '
1789       identifier_append(sql, Array(target))
1790       if conflict_where = opts[:conflict_where]
1791         sql << " WHERE "
1792         literal_append(sql, conflict_where)
1793       end
1794     end
1795 
1796     if values = opts[:update]
1797       sql << " DO UPDATE SET "
1798       update_sql_values_hash(sql, values)
1799       if update_where = opts[:update_where]
1800         sql << " WHERE "
1801         literal_append(sql, update_where)
1802       end
1803     else
1804       sql << " DO NOTHING"
1805     end
1806   end
1807 end
insert_into_sql(sql) click to toggle source

Include aliases when inserting into a single table on PostgreSQL 9.5+.

     # File lib/sequel/adapters/shared/postgres.rb
1810 def insert_into_sql(sql)
1811   sql << " INTO "
1812   if (f = @opts[:from]) && f.length == 1
1813     identifier_append(sql, server_version >= 90500 ? f.first : unaliased_identifier(f.first))
1814   else
1815     source_list_append(sql, f)
1816   end
1817 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
1820 def insert_pk
1821   if (f = opts[:from]) && !f.empty?
1822     case t = f.first
1823     when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier
1824       if pk = db.primary_key(t)
1825         Sequel::SQL::Identifier.new(pk)
1826       end
1827     end
1828   end
1829 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
1832 def insert_values_sql(sql)
1833   case opts[:override]
1834   when :system
1835     sql << " OVERRIDING SYSTEM VALUE"
1836   when :user
1837     sql << " OVERRIDING USER VALUE"
1838   end
1839   super
1840 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
1844 def join_from_sql(type, sql)
1845   if(from = @opts[:from][1..-1]).empty?
1846     raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join]
1847   else
1848     sql << ' ' << type.to_s << ' '
1849     source_list_append(sql, from)
1850     select_join_sql(sql)
1851   end
1852 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
1855 def literal_blob_append(sql, v)
1856   sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'"
1857 end
literal_false() click to toggle source

PostgreSQL uses FALSE for false values

     # File lib/sequel/adapters/shared/postgres.rb
1860 def literal_false
1861   'false'
1862 end
literal_float(value) click to toggle source

PostgreSQL quotes NaN and Infinity.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1865 def literal_float(value)
1866   if value.finite?
1867     super
1868   elsif value.nan?
1869     "'NaN'"
1870   elsif value.infinite? == 1
1871     "'Infinity'"
1872   else
1873     "'-Infinity'"
1874   end
1875 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
1878 def literal_string_append(sql, v)
1879   sql << "'" << v.gsub("'", "''") << "'"
1880 end
literal_true() click to toggle source

PostgreSQL uses true for true values

     # File lib/sequel/adapters/shared/postgres.rb
1883 def literal_true
1884   'true'
1885 end
multi_insert_sql_strategy() click to toggle source

PostgreSQL supports multiple rows in INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
1888 def multi_insert_sql_strategy
1889   :values
1890 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
1893 def non_sql_option?(key)
1894   super || key == :cursor || key == :insert_conflict
1895 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
1907 def requires_like_escape?
1908   false
1909 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
1913 def select_lock_sql(sql)
1914   lock = @opts[:lock]
1915   if lock == :share
1916     sql << ' FOR SHARE'
1917   else
1918     super
1919   end
1920 
1921   if lock
1922     if @opts[:skip_locked]
1923       sql << " SKIP LOCKED"
1924     elsif @opts[:nowait]
1925       sql << " NOWAIT"
1926     end
1927   end
1928 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
1931 def select_values_sql(sql)
1932   sql << "VALUES "
1933   expression_list_append(sql, opts[:values])
1934 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
1937 def select_with_sql_base
1938   opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
1939 end
select_with_sql_prefix(sql, w) click to toggle source

Support WITH AS [NOT] MATERIALIZED if :materialized option is used.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1942 def select_with_sql_prefix(sql, w)
1943   super
1944 
1945   case w[:materialized]
1946   when true
1947     sql << "MATERIALIZED "
1948   when false
1949     sql << "NOT MATERIALIZED "
1950   end
1951 end
server_version() click to toggle source

The version of the database server

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

PostgreSQL supports quoted function names.

     # File lib/sequel/adapters/shared/postgres.rb
1959 def supports_quoted_function_names?
1960   true
1961 end
to_prepared_statement(type, *a) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1963 def to_prepared_statement(type, *a)
1964   if type == :insert && !@opts.has_key?(:returning)
1965     returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a)
1966   else
1967     super
1968   end
1969 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
1980 def update_from_sql(sql)
1981   join_from_sql(:FROM, sql)
1982 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
1985 def update_table_sql(sql)
1986   sql << ' '
1987   source_list_append(sql, @opts[:from][0..0])
1988 end