module Sequel::Postgres::DatasetMethods
Constants
- LOCK_MODES
- NULL
Public Instance Methods
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
Handle converting the ruby xor operator (^) into the PostgreSQL xor operator (#), and use the ILIKE and NOT ILIKE operators.
# 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
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
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
Run a full text search on PostgreSQL. By default, searching for the inclusion of any of the terms in any of the cols.
Options:
- :headline
-
Append a expression to the selected columns aliased to headline that contains an extract of the matched text.
- :language
-
The language to use for the search (default: 'simple')
- :plain
-
Whether a plain search should be used (default: false). In this case, terms should be a single string, and it will do a search where cols contains all of the words in terms. This ignores search operators in terms.
- :phrase
-
Similar to :plain, but also adding an ILIKE filter to ensure that returned rows also include the exact phrase used.
- :rank
-
Set to true to order by the rank, so that closer matches are returned first.
- :to_tsquery
-
Can be set to :plain or :phrase to specify the function to use to convert the terms to a ts_query.
- :tsquery
-
Specifies the terms argument is already a valid
SQL
expression returning a tsquery, and can be used directly in the query. - :tsvector
-
Specifies the cols argument is already a valid
SQL
expression returning a tsvector, and can be used directly in the query.
# File lib/sequel/adapters/shared/postgres.rb 1612 def full_text_search(cols, terms, opts = OPTS) 1613 lang = Sequel.cast(opts[:language] || 'simple', :regconfig) 1614 1615 unless opts[:tsvector] 1616 phrase_cols = full_text_string_join(cols) 1617 cols = Sequel.function(:to_tsvector, lang, phrase_cols) 1618 end 1619 1620 unless opts[:tsquery] 1621 phrase_terms = terms.is_a?(Array) ? terms.join(' | ') : terms 1622 1623 query_func = case to_tsquery = opts[:to_tsquery] 1624 when :phrase, :plain 1625 :"#{to_tsquery}to_tsquery" 1626 else 1627 (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery 1628 end 1629 1630 terms = Sequel.function(query_func, lang, phrase_terms) 1631 end 1632 1633 ds = where(Sequel.lit(["", " @@ ", ""], cols, terms)) 1634 1635 if opts[:phrase] 1636 raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery] 1637 ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true) 1638 end 1639 1640 if opts[:rank] 1641 ds = ds.reverse{ts_rank_cd(cols, terms)} 1642 end 1643 1644 if opts[:headline] 1645 ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)} 1646 end 1647 1648 ds 1649 end
Insert given values into the database.
# 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
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
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 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
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
Support SQL::AliasedExpression
as expr to setup a USING join with a table alias for the USING columns.
# 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
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
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
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
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
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
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
# 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
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
DISTINCT ON is a PostgreSQL extension
# File lib/sequel/adapters/shared/postgres.rb 1826 def supports_distinct_on? 1827 true 1828 end
PostgreSQL 9.5+ supports GROUP CUBE
# File lib/sequel/adapters/shared/postgres.rb 1831 def supports_group_cube? 1832 server_version >= 90500 1833 end
PostgreSQL 9.5+ supports GROUP ROLLUP
# File lib/sequel/adapters/shared/postgres.rb 1836 def supports_group_rollup? 1837 server_version >= 90500 1838 end
PostgreSQL 9.5+ supports GROUPING SETS
# File lib/sequel/adapters/shared/postgres.rb 1841 def supports_grouping_sets? 1842 server_version >= 90500 1843 end
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
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
PostgreSQL 9.3+ supports lateral subqueries
# File lib/sequel/adapters/shared/postgres.rb 1856 def supports_lateral_subqueries? 1857 server_version >= 90300 1858 end
PostgreSQL 15+ supports MERGE.
# File lib/sequel/adapters/shared/postgres.rb 1866 def supports_merge? 1867 server_version >= 150000 1868 end
PostgreSQL supports modifying joined datasets
# File lib/sequel/adapters/shared/postgres.rb 1861 def supports_modifying_joins? 1862 true 1863 end
PostgreSQL supports NOWAIT.
# File lib/sequel/adapters/shared/postgres.rb 1871 def supports_nowait? 1872 true 1873 end
PostgreSQL supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/postgres.rb 1881 def supports_regexp? 1882 true 1883 end
Returning is always supported.
# File lib/sequel/adapters/shared/postgres.rb 1876 def supports_returning?(type) 1877 true 1878 end
PostgreSQL 9.5+ supports SKIP LOCKED.
# File lib/sequel/adapters/shared/postgres.rb 1886 def supports_skip_locked? 1887 server_version >= 90500 1888 end
PostgreSQL supports timezones in literal timestamps
# File lib/sequel/adapters/shared/postgres.rb 1891 def supports_timestamp_timezones? 1892 true 1893 end
PostgreSQL 8.4+ supports WINDOW clause.
# File lib/sequel/adapters/shared/postgres.rb 1896 def supports_window_clause? 1897 server_version >= 80400 1898 end
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
PostgreSQL 8.4+ supports window functions
# File lib/sequel/adapters/shared/postgres.rb 1901 def supports_window_functions? 1902 server_version >= 80400 1903 end
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
# 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
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
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.
# 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
# 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
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
# File lib/sequel/adapters/shared/postgres.rb 1991 def _merge_matched_sql(sql, data) 1992 sql << " THEN DO NOTHING" 1993 end
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
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
PostgreSQL requires parentheses around compound datasets if they use CTEs, and using them in other places doesn't hurt.
# File lib/sequel/adapters/shared/postgres.rb 2149 def compound_dataset_sql_append(sql, ds) 2150 sql << '(' 2151 super 2152 sql << ')' 2153 end
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
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
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
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
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
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
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
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
Support table aliases for USING columns
# 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
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
PostgreSQL uses FALSE for false values
# File lib/sequel/adapters/shared/postgres.rb 2110 def literal_false 2111 'false' 2112 end
PostgreSQL quotes NaN and Infinity.
# 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
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
PostgreSQL uses true for true values
# File lib/sequel/adapters/shared/postgres.rb 2133 def literal_true 2134 'true' 2135 end
PostgreSQL supports multiple rows in INSERT.
# File lib/sequel/adapters/shared/postgres.rb 2138 def multi_insert_sql_strategy 2139 :values 2140 end
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
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
Support FOR SHARE locking when using the :share lock style. Use SKIP LOCKED if skipping locked rows.
# 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
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
Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive
# 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
Support PostgreSQL 14+ CTE SEARCH/CYCLE clauses
# 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
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
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
PostgreSQL supports quoted function names.
# File lib/sequel/adapters/shared/postgres.rb 2265 def supports_quoted_function_names? 2266 true 2267 end
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
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