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
1535 def analyze
1536   explain(:analyze=>true)
1537 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
1542 def complex_expression_sql_append(sql, op, args)
1543   case op
1544   when :^
1545     j = ' # '
1546     c = false
1547     args.each do |a|
1548       sql << j if c
1549       literal_append(sql, a)
1550       c ||= true
1551     end
1552   when :ILIKE, :'NOT ILIKE'
1553     sql << '('
1554     literal_append(sql, args[0])
1555     sql << ' ' << op.to_s << ' '
1556     literal_append(sql, args[1])
1557     sql << " ESCAPE "
1558     literal_append(sql, "\\")
1559     sql << ')'
1560   else
1561     super
1562   end
1563 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
1579 def disable_insert_returning
1580   clone(:disable_insert_returning=>true)
1581 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
1584 def explain(opts=OPTS)
1585   with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map(:'QUERY PLAN').join("\r\n")
1586 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
1589 def for_share
1590   lock_style(:share)
1591 end
insert(*values) click to toggle source

Insert given values into the database.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1652 def insert(*values)
1653   if @opts[:returning]
1654     # Already know which columns to return, let the standard code handle it
1655     super
1656   elsif @opts[:sql] || @opts[:disable_insert_returning]
1657     # Raw SQL used or RETURNING disabled, just use the default behavior
1658     # and return nil since sequence is not known.
1659     super
1660     nil
1661   else
1662     # Force the use of RETURNING with the primary key value,
1663     # unless it has been disabled.
1664     returning(insert_pk).insert(*values){|r| return r.values.first}
1665   end
1666 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
1703 def insert_conflict(opts=OPTS)
1704   clone(:insert_conflict => opts)
1705 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
1713 def insert_ignore
1714   insert_conflict
1715 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
1720 def insert_select(*values)
1721   return unless supports_insert_select?
1722   # Handle case where query does not return a row
1723   server?(:default).with_sql_first(insert_select_sql(*values)) || false
1724 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
1728 def insert_select_sql(*values)
1729   ds = opts[:returning] ? self : returning
1730   ds.insert_sql(*values)
1731 end
join_table(type, table, expr=nil, options=OPTS, &block) click to toggle source

Support SQL::AliasedExpression as expr to setup a USING join with a table alias for the USING columns.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1735 def join_table(type, table, expr=nil, options=OPTS, &block)
1736   if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all?
1737     options = options.merge(:join_using=>true)
1738   end
1739   super
1740 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
1747 def lock(mode, opts=OPTS)
1748   if defined?(yield) # perform locking inside a transaction and yield to block
1749     @db.transaction(opts){lock(mode, opts); yield}
1750   else
1751     sql = 'LOCK TABLE '.dup
1752     source_list_append(sql, @opts[:from])
1753     mode = mode.to_s.upcase.strip
1754     unless LOCK_MODES.include?(mode)
1755       raise Error, "Unsupported lock mode: #{mode}"
1756     end
1757     sql << " IN #{mode} MODE"
1758     @db.execute(sql, opts)
1759   end
1760   nil
1761 end
merge_do_nothing_when_matched(&block) click to toggle source

Return a dataset with a WHEN MATCHED THEN DO NOTHING clause added to the MERGE statement. If a block is passed, treat it as a virtual row and use it as additional conditions for the match.

merge_do_nothing_when_matched
# WHEN MATCHED THEN DO NOTHING

merge_do_nothing_when_matched{a > 30}
# WHEN MATCHED AND (a > 30) THEN DO NOTHING
     # File lib/sequel/adapters/shared/postgres.rb
1772 def merge_do_nothing_when_matched(&block)
1773   _merge_when(:type=>:matched, &block)
1774 end
merge_do_nothing_when_not_matched(&block) click to toggle source

Return a dataset with a WHEN NOT MATCHED THEN DO NOTHING clause added to the MERGE statement. If a block is passed, treat it as a virtual row and use it as additional conditions for the match.

merge_do_nothing_when_not_matched
# WHEN NOT MATCHED THEN DO NOTHING

merge_do_nothing_when_not_matched{a > 30}
# WHEN NOT MATCHED AND (a > 30) THEN DO NOTHING
     # File lib/sequel/adapters/shared/postgres.rb
1785 def merge_do_nothing_when_not_matched(&block)
1786   _merge_when(:type=>:not_matched, &block)
1787 end
merge_insert(*values, &block) click to toggle source

Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
1790 def merge_insert(*values, &block)
1791   h = {:type=>:insert, :values=>values}
1792   if override = @opts[:override]
1793     h[:override] = insert_override_sql(String.new)
1794   end
1795   _merge_when(h, &block)
1796 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
1801 def overriding_system_value
1802   clone(:override=>:system)
1803 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
1807 def overriding_user_value
1808   clone(:override=>:user)
1809 end
supports_cte?(type=:select) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1811 def supports_cte?(type=:select)
1812   if type == :select
1813     server_version >= 80400
1814   else
1815     server_version >= 90100
1816   end
1817 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
1821 def supports_cte_in_subqueries?
1822   supports_cte?
1823 end
supports_distinct_on?() click to toggle source

DISTINCT ON is a PostgreSQL extension

     # File lib/sequel/adapters/shared/postgres.rb
1826 def supports_distinct_on?
1827   true
1828 end
supports_group_cube?() click to toggle source

PostgreSQL 9.5+ supports GROUP CUBE

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

PostgreSQL 9.5+ supports GROUP ROLLUP

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

PostgreSQL 9.5+ supports GROUPING SETS

     # File lib/sequel/adapters/shared/postgres.rb
1841 def supports_grouping_sets?
1842   server_version >= 90500
1843 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
1851 def supports_insert_conflict?
1852   server_version >= 90500
1853 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
1846 def supports_insert_select?
1847   !@opts[:disable_insert_returning]
1848 end
supports_lateral_subqueries?() click to toggle source

PostgreSQL 9.3+ supports lateral subqueries

     # File lib/sequel/adapters/shared/postgres.rb
1856 def supports_lateral_subqueries?
1857   server_version >= 90300
1858 end
supports_merge?() click to toggle source

PostgreSQL 15+ supports MERGE.

     # File lib/sequel/adapters/shared/postgres.rb
1866 def supports_merge?
1867   server_version >= 150000
1868 end
supports_modifying_joins?() click to toggle source

PostgreSQL supports modifying joined datasets

     # File lib/sequel/adapters/shared/postgres.rb
1861 def supports_modifying_joins?
1862   true
1863 end
supports_nowait?() click to toggle source

PostgreSQL supports NOWAIT.

     # File lib/sequel/adapters/shared/postgres.rb
1871 def supports_nowait?
1872   true
1873 end
supports_regexp?() click to toggle source

PostgreSQL supports pattern matching via regular expressions

     # File lib/sequel/adapters/shared/postgres.rb
1881 def supports_regexp?
1882   true
1883 end
supports_returning?(type) click to toggle source

Returning is always supported.

     # File lib/sequel/adapters/shared/postgres.rb
1876 def supports_returning?(type)
1877   true
1878 end
supports_skip_locked?() click to toggle source

PostgreSQL 9.5+ supports SKIP LOCKED.

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

PostgreSQL supports timezones in literal timestamps

     # File lib/sequel/adapters/shared/postgres.rb
1891 def supports_timestamp_timezones?
1892   true
1893 end
supports_window_clause?() click to toggle source

PostgreSQL 8.4+ supports WINDOW clause.

     # File lib/sequel/adapters/shared/postgres.rb
1896 def supports_window_clause?
1897   server_version >= 80400
1898 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
1907 def supports_window_function_frame_option?(option)
1908   case option
1909   when :rows, :range
1910     true
1911   when :offset
1912     server_version >= 90000
1913   when :groups, :exclude
1914     server_version >= 110000
1915   end
1916 end
supports_window_functions?() click to toggle source

PostgreSQL 8.4+ supports window functions

     # File lib/sequel/adapters/shared/postgres.rb
1901 def supports_window_functions?
1902   server_version >= 80400
1903 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
1934 def truncate(opts = OPTS)
1935   if opts.empty?
1936     super()
1937   else
1938     clone(:truncate_opts=>opts).truncate
1939   end
1940 end
with_ties() click to toggle source

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.

     # File lib/sequel/adapters/shared/postgres.rb
1945 def with_ties
1946   clone(:limit_with_ties=>true)
1947 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
1955 def _import(columns, values, opts=OPTS)
1956   if @opts[:returning]
1957     statements = multi_insert_sql(columns, values)
1958     trans_opts = Hash[opts]
1959     trans_opts[:server] = @opts[:server]
1960     @db.transaction(trans_opts) do
1961       statements.map{|st| returning_fetch_rows(st)}
1962     end.first.map{|v| v.length == 1 ? v.values.first : v}
1963   elsif opts[:return] == :primary_key
1964     returning(insert_pk)._import(columns, values, opts)
1965   else
1966     super
1967   end
1968 end
to_prepared_statement(type, *a) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1970 def to_prepared_statement(type, *a)
1971   if type == :insert && !@opts.has_key?(:returning)
1972     returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a)
1973   else
1974     super
1975   end
1976 end

Private Instance Methods

_merge_insert_sql(sql, data) click to toggle source

Append the INSERT sql used in a MERGE

     # File lib/sequel/adapters/shared/postgres.rb
1981 def _merge_insert_sql(sql, data)
1982   sql << " THEN INSERT "
1983   columns, values = _parse_insert_sql_args(data[:values])
1984   _insert_columns_sql(sql, columns)
1985   if override = data[:override]
1986     sql << override
1987   end
1988   _insert_values_sql(sql, values)
1989 end
_merge_matched_sql(sql, data) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1991 def _merge_matched_sql(sql, data)
1992   sql << " THEN DO NOTHING"
1993 end
Also aliased as: _merge_not_matched_sql
_merge_not_matched_sql(sql, data)
Alias for: _merge_matched_sql
_truncate_sql(table) click to toggle source

Format TRUNCATE statement with PostgreSQL specific options.

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

Allow truncation of multiple source tables.

     # File lib/sequel/adapters/shared/postgres.rb
2003 def check_truncation_allowed!
2004   raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group]
2005   raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join]
2006 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
2149 def compound_dataset_sql_append(sql, ds)
2150   sql << '('
2151   super
2152   sql << ')'
2153 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
2009 def delete_from_sql(sql)
2010   sql << ' FROM '
2011   source_list_append(sql, @opts[:from][0..0])
2012 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
2015 def delete_using_sql(sql)
2016   join_from_sql(:USING, sql)
2017 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
2270 def full_text_string_join(cols)
2271   cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')}
2272   cols = cols.zip([' '] * cols.length).flatten
2273   cols.pop
2274   SQL::StringExpression.new(:'||', *cols)
2275 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
2020 def insert_conflict_sql(sql)
2021   if opts = @opts[:insert_conflict]
2022     sql << " ON CONFLICT"
2023 
2024     if target = opts[:constraint] 
2025       sql << " ON CONSTRAINT "
2026       identifier_append(sql, target)
2027     elsif target = opts[:target]
2028       sql << ' '
2029       identifier_append(sql, Array(target))
2030       if conflict_where = opts[:conflict_where]
2031         sql << " WHERE "
2032         literal_append(sql, conflict_where)
2033       end
2034     end
2035 
2036     if values = opts[:update]
2037       sql << " DO UPDATE SET "
2038       update_sql_values_hash(sql, values)
2039       if update_where = opts[:update_where]
2040         sql << " WHERE "
2041         literal_append(sql, update_where)
2042       end
2043     else
2044       sql << " DO NOTHING"
2045     end
2046   end
2047 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
2050 def insert_into_sql(sql)
2051   sql << " INTO "
2052   if (f = @opts[:from]) && f.length == 1
2053     identifier_append(sql, server_version >= 90500 ? f.first : unaliased_identifier(f.first))
2054   else
2055     source_list_append(sql, f)
2056   end
2057 end
insert_override_sql(sql) click to toggle source

Support OVERRIDING SYSTEM|USER VALUE in insert statements

     # File lib/sequel/adapters/shared/postgres.rb
2072 def insert_override_sql(sql)
2073   case opts[:override]
2074   when :system
2075     sql << " OVERRIDING SYSTEM VALUE"
2076   when :user
2077     sql << " OVERRIDING USER VALUE"
2078   end
2079 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
2060 def insert_pk
2061   if (f = opts[:from]) && !f.empty?
2062     case t = f.first
2063     when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier
2064       if pk = db.primary_key(t)
2065         Sequel::SQL::Identifier.new(pk)
2066       end
2067     end
2068   end
2069 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
2083 def join_from_sql(type, sql)
2084   if(from = @opts[:from][1..-1]).empty?
2085     raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join]
2086   else
2087     sql << ' ' << type.to_s << ' '
2088     source_list_append(sql, from)
2089     select_join_sql(sql)
2090   end
2091 end
join_using_clause_using_sql_append(sql, using_columns) click to toggle source

Support table aliases for USING columns

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2094 def join_using_clause_using_sql_append(sql, using_columns)
2095   if using_columns.is_a?(SQL::AliasedExpression)
2096     super(sql, using_columns.expression)
2097     sql << ' AS '
2098     identifier_append(sql, using_columns.alias)
2099   else
2100     super
2101   end
2102 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
2105 def literal_blob_append(sql, v)
2106   sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'"
2107 end
literal_false() click to toggle source

PostgreSQL uses FALSE for false values

     # File lib/sequel/adapters/shared/postgres.rb
2110 def literal_false
2111   'false'
2112 end
literal_float(value) click to toggle source

PostgreSQL quotes NaN and Infinity.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2115 def literal_float(value)
2116   if value.finite?
2117     super
2118   elsif value.nan?
2119     "'NaN'"
2120   elsif value.infinite? == 1
2121     "'Infinity'"
2122   else
2123     "'-Infinity'"
2124   end
2125 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
2128 def literal_string_append(sql, v)
2129   sql << "'" << v.gsub("'", "''") << "'"
2130 end
literal_true() click to toggle source

PostgreSQL uses true for true values

     # File lib/sequel/adapters/shared/postgres.rb
2133 def literal_true
2134   'true'
2135 end
multi_insert_sql_strategy() click to toggle source

PostgreSQL supports multiple rows in INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
2138 def multi_insert_sql_strategy
2139   :values
2140 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
2143 def non_sql_option?(key)
2144   super || key == :cursor || key == :insert_conflict
2145 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
2157 def requires_like_escape?
2158   false
2159 end
select_limit_sql(sql) click to toggle source

Support FETCH FIRST WITH TIES on PostgreSQL 13+.

     # File lib/sequel/adapters/shared/postgres.rb
2162 def select_limit_sql(sql)
2163   l = @opts[:limit]
2164   o = @opts[:offset]
2165 
2166   return unless l || o
2167 
2168   if @opts[:limit_with_ties]
2169     if o
2170       sql << " OFFSET "
2171       literal_append(sql, o)
2172     end
2173 
2174     if l
2175       sql << " FETCH FIRST "
2176       literal_append(sql, l)
2177       sql << " ROWS WITH TIES"
2178     end
2179   else
2180     if l
2181       sql << " LIMIT "
2182       literal_append(sql, l)
2183     end
2184 
2185     if o
2186       sql << " OFFSET "
2187       literal_append(sql, o)
2188     end
2189   end
2190 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
2194 def select_lock_sql(sql)
2195   lock = @opts[:lock]
2196   if lock == :share
2197     sql << ' FOR SHARE'
2198   else
2199     super
2200   end
2201 
2202   if lock
2203     if @opts[:skip_locked]
2204       sql << " SKIP LOCKED"
2205     elsif @opts[:nowait]
2206       sql << " NOWAIT"
2207     end
2208   end
2209 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
2212 def select_values_sql(sql)
2213   sql << "VALUES "
2214   expression_list_append(sql, opts[:values])
2215 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
2218 def select_with_sql_base
2219   opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
2220 end
select_with_sql_cte(sql, cte) click to toggle source

Support PostgreSQL 14+ CTE SEARCH/CYCLE clauses

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2223 def select_with_sql_cte(sql, cte)
2224   super
2225 
2226   if search_opts = cte[:search]
2227     sql << if search_opts[:type] == :breadth
2228       " SEARCH BREADTH FIRST BY "
2229     else
2230       " SEARCH DEPTH FIRST BY "
2231     end
2232 
2233     identifier_list_append(sql, Array(search_opts[:by]))
2234     sql << " SET "
2235     identifier_append(sql, search_opts[:set] || :ordercol)
2236   end
2237 
2238   if cycle_opts = cte[:cycle]
2239     sql << " CYCLE "
2240     identifier_list_append(sql, Array(cycle_opts[:columns]))
2241     sql << " SET "
2242     identifier_append(sql, cycle_opts[:cycle_column] || :is_cycle)
2243     if cycle_opts.has_key?(:cycle_value)
2244       sql << " TO "
2245       literal_append(sql, cycle_opts[:cycle_value])
2246       sql << " DEFAULT "
2247       literal_append(sql, cycle_opts.fetch(:noncycle_value, false))
2248     end
2249     sql << " USING "
2250     identifier_append(sql, cycle_opts[:path_column] || :path)
2251   end
2252 end
server_version() click to toggle source

The version of the database server

     # File lib/sequel/adapters/shared/postgres.rb
2255 def server_version
2256   db.server_version(@opts[:server])
2257 end
supports_filtered_aggregates?() click to toggle source

PostgreSQL 9.4+ supports the FILTER clause for aggregate functions.

     # File lib/sequel/adapters/shared/postgres.rb
2260 def supports_filtered_aggregates?
2261   server_version >= 90400
2262 end
supports_quoted_function_names?() click to toggle source

PostgreSQL supports quoted function names.

     # File lib/sequel/adapters/shared/postgres.rb
2265 def supports_quoted_function_names?
2266   true
2267 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
2278 def update_from_sql(sql)
2279   join_from_sql(:FROM, sql)
2280 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
2283 def update_table_sql(sql)
2284   sql << ' '
2285   source_list_append(sql, @opts[:from][0..0])
2286 end