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 1661 def analyze 1662 explain(:analyze=>true) 1663 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 1668 def complex_expression_sql_append(sql, op, args) 1669 case op 1670 when :^ 1671 j = ' # ' 1672 c = false 1673 args.each do |a| 1674 sql << j if c 1675 literal_append(sql, a) 1676 c ||= true 1677 end 1678 when :ILIKE, :'NOT ILIKE' 1679 sql << '(' 1680 literal_append(sql, args[0]) 1681 sql << ' ' << op.to_s << ' ' 1682 literal_append(sql, args[1]) 1683 sql << " ESCAPE " 1684 literal_append(sql, "\\") 1685 sql << ')' 1686 else 1687 super 1688 end 1689 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 1705 def disable_insert_returning 1706 clone(:disable_insert_returning=>true) 1707 end
Return the results of an EXPLAIN query as a string
# File lib/sequel/adapters/shared/postgres.rb 1710 def explain(opts=OPTS) 1711 with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map(:'QUERY PLAN').join("\r\n") 1712 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 1738 def full_text_search(cols, terms, opts = OPTS) 1739 lang = Sequel.cast(opts[:language] || 'simple', :regconfig) 1740 1741 unless opts[:tsvector] 1742 phrase_cols = full_text_string_join(cols) 1743 cols = Sequel.function(:to_tsvector, lang, phrase_cols) 1744 end 1745 1746 unless opts[:tsquery] 1747 phrase_terms = terms.is_a?(Array) ? terms.join(' | ') : terms 1748 1749 query_func = case to_tsquery = opts[:to_tsquery] 1750 when :phrase, :plain 1751 :"#{to_tsquery}to_tsquery" 1752 else 1753 (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery 1754 end 1755 1756 terms = Sequel.function(query_func, lang, phrase_terms) 1757 end 1758 1759 ds = where(Sequel.lit(["", " @@ ", ""], cols, terms)) 1760 1761 if opts[:phrase] 1762 raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery] 1763 ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true) 1764 end 1765 1766 if opts[:rank] 1767 ds = ds.reverse{ts_rank_cd(cols, terms)} 1768 end 1769 1770 if opts[:headline] 1771 ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)} 1772 end 1773 1774 ds 1775 end
Insert given values into the database.
# File lib/sequel/adapters/shared/postgres.rb 1778 def insert(*values) 1779 if @opts[:returning] 1780 # Already know which columns to return, let the standard code handle it 1781 super 1782 elsif @opts[:sql] || @opts[:disable_insert_returning] 1783 # Raw SQL used or RETURNING disabled, just use the default behavior 1784 # and return nil since sequence is not known. 1785 super 1786 nil 1787 else 1788 # Force the use of RETURNING with the primary key value, 1789 # unless it has been disabled. 1790 returning(insert_pk).insert(*values){|r| return r.values.first} 1791 end 1792 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 1829 def insert_conflict(opts=OPTS) 1830 clone(:insert_conflict => opts) 1831 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 1839 def insert_ignore 1840 insert_conflict 1841 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 1846 def insert_select(*values) 1847 return unless supports_insert_select? 1848 # Handle case where query does not return a row 1849 server?(:default).with_sql_first(insert_select_sql(*values)) || false 1850 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 1854 def insert_select_sql(*values) 1855 ds = opts[:returning] ? self : returning 1856 ds.insert_sql(*values) 1857 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 1861 def join_table(type, table, expr=nil, options=OPTS, &block) 1862 if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all? 1863 options = options.merge(:join_using=>true) 1864 end 1865 super 1866 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 1873 def lock(mode, opts=OPTS) 1874 if defined?(yield) # perform locking inside a transaction and yield to block 1875 @db.transaction(opts){lock(mode, opts); yield} 1876 else 1877 sql = 'LOCK TABLE '.dup 1878 source_list_append(sql, @opts[:from]) 1879 mode = mode.to_s.upcase.strip 1880 unless LOCK_MODES.include?(mode) 1881 raise Error, "Unsupported lock mode: #{mode}" 1882 end 1883 sql << " IN #{mode} MODE" 1884 @db.execute(sql, opts) 1885 end 1886 nil 1887 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 1898 def merge_do_nothing_when_matched(&block) 1899 _merge_when(:type=>:matched, &block) 1900 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 1911 def merge_do_nothing_when_not_matched(&block) 1912 _merge_when(:type=>:not_matched, &block) 1913 end
Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.
# File lib/sequel/adapters/shared/postgres.rb 1916 def merge_insert(*values, &block) 1917 h = {:type=>:insert, :values=>values} 1918 if override = @opts[:override] 1919 h[:override] = insert_override_sql(String.new) 1920 end 1921 _merge_when(h, &block) 1922 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 1927 def overriding_system_value 1928 clone(:override=>:system) 1929 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 1933 def overriding_user_value 1934 clone(:override=>:user) 1935 end
# File lib/sequel/adapters/shared/postgres.rb 1937 def supports_cte?(type=:select) 1938 if type == :select 1939 server_version >= 80400 1940 else 1941 server_version >= 90100 1942 end 1943 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 1947 def supports_cte_in_subqueries? 1948 supports_cte? 1949 end
DISTINCT ON is a PostgreSQL extension
# File lib/sequel/adapters/shared/postgres.rb 1952 def supports_distinct_on? 1953 true 1954 end
PostgreSQL 9.5+ supports GROUP CUBE
# File lib/sequel/adapters/shared/postgres.rb 1957 def supports_group_cube? 1958 server_version >= 90500 1959 end
PostgreSQL 9.5+ supports GROUP ROLLUP
# File lib/sequel/adapters/shared/postgres.rb 1962 def supports_group_rollup? 1963 server_version >= 90500 1964 end
PostgreSQL 9.5+ supports GROUPING SETS
# File lib/sequel/adapters/shared/postgres.rb 1967 def supports_grouping_sets? 1968 server_version >= 90500 1969 end
PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.
# File lib/sequel/adapters/shared/postgres.rb 1977 def supports_insert_conflict? 1978 server_version >= 90500 1979 end
True unless insert returning has been disabled for this dataset.
# File lib/sequel/adapters/shared/postgres.rb 1972 def supports_insert_select? 1973 !@opts[:disable_insert_returning] 1974 end
PostgreSQL 9.3+ supports lateral subqueries
# File lib/sequel/adapters/shared/postgres.rb 1982 def supports_lateral_subqueries? 1983 server_version >= 90300 1984 end
PostgreSQL 15+ supports MERGE.
# File lib/sequel/adapters/shared/postgres.rb 1992 def supports_merge? 1993 server_version >= 150000 1994 end
PostgreSQL supports modifying joined datasets
# File lib/sequel/adapters/shared/postgres.rb 1987 def supports_modifying_joins? 1988 true 1989 end
PostgreSQL supports NOWAIT.
# File lib/sequel/adapters/shared/postgres.rb 1997 def supports_nowait? 1998 true 1999 end
PostgreSQL supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/postgres.rb 2007 def supports_regexp? 2008 true 2009 end
Returning is always supported.
# File lib/sequel/adapters/shared/postgres.rb 2002 def supports_returning?(type) 2003 true 2004 end
PostgreSQL 9.5+ supports SKIP LOCKED.
# File lib/sequel/adapters/shared/postgres.rb 2012 def supports_skip_locked? 2013 server_version >= 90500 2014 end
PostgreSQL supports timezones in literal timestamps
# File lib/sequel/adapters/shared/postgres.rb 2017 def supports_timestamp_timezones? 2018 true 2019 end
PostgreSQL 8.4+ supports WINDOW clause.
# File lib/sequel/adapters/shared/postgres.rb 2022 def supports_window_clause? 2023 server_version >= 80400 2024 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 2033 def supports_window_function_frame_option?(option) 2034 case option 2035 when :rows, :range 2036 true 2037 when :offset 2038 server_version >= 90000 2039 when :groups, :exclude 2040 server_version >= 110000 2041 else 2042 false 2043 end 2044 end
PostgreSQL 8.4+ supports window functions
# File lib/sequel/adapters/shared/postgres.rb 2027 def supports_window_functions? 2028 server_version >= 80400 2029 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 2062 def truncate(opts = OPTS) 2063 if opts.empty? 2064 super() 2065 else 2066 clone(:truncate_opts=>opts).truncate 2067 end 2068 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 2073 def with_ties 2074 clone(:limit_with_ties=>true) 2075 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 2083 def _import(columns, values, opts=OPTS) 2084 if @opts[:returning] 2085 # no transaction: our multi_insert_sql_strategy should guarantee 2086 # that there's only ever a single statement. 2087 sql = multi_insert_sql(columns, values)[0] 2088 returning_fetch_rows(sql).map{|v| v.length == 1 ? v.values.first : v} 2089 elsif opts[:return] == :primary_key 2090 returning(insert_pk)._import(columns, values, opts) 2091 else 2092 super 2093 end 2094 end
# File lib/sequel/adapters/shared/postgres.rb 2096 def to_prepared_statement(type, *a) 2097 if type == :insert && !@opts.has_key?(:returning) 2098 returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a) 2099 else 2100 super 2101 end 2102 end
Private Instance Methods
Append the INSERT sql used in a MERGE
# File lib/sequel/adapters/shared/postgres.rb 2107 def _merge_insert_sql(sql, data) 2108 sql << " THEN INSERT " 2109 columns, values = _parse_insert_sql_args(data[:values]) 2110 _insert_columns_sql(sql, columns) 2111 if override = data[:override] 2112 sql << override 2113 end 2114 _insert_values_sql(sql, values) 2115 end
# File lib/sequel/adapters/shared/postgres.rb 2117 def _merge_matched_sql(sql, data) 2118 sql << " THEN DO NOTHING" 2119 end
Format TRUNCATE statement with PostgreSQL specific options.
# File lib/sequel/adapters/shared/postgres.rb 2123 def _truncate_sql(table) 2124 to = @opts[:truncate_opts] || OPTS 2125 "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}" 2126 end
Allow truncation of multiple source tables.
# File lib/sequel/adapters/shared/postgres.rb 2129 def check_truncation_allowed! 2130 raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group] 2131 raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join] 2132 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 2290 def compound_dataset_sql_append(sql, ds) 2291 sql << '(' 2292 super 2293 sql << ')' 2294 end
Only include the primary table in the main delete clause
# File lib/sequel/adapters/shared/postgres.rb 2135 def delete_from_sql(sql) 2136 sql << ' FROM ' 2137 source_list_append(sql, @opts[:from][0..0]) 2138 end
Use USING to specify additional tables in a delete query
# File lib/sequel/adapters/shared/postgres.rb 2141 def delete_using_sql(sql) 2142 join_from_sql(:USING, sql) 2143 end
Concatenate the expressions with a space in between
# File lib/sequel/adapters/shared/postgres.rb 2414 def full_text_string_join(cols) 2415 cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')} 2416 cols = cols.zip([' '] * cols.length).flatten 2417 cols.pop 2418 SQL::StringExpression.new(:'||', *cols) 2419 end
Add ON CONFLICT clause if it should be used
# File lib/sequel/adapters/shared/postgres.rb 2146 def insert_conflict_sql(sql) 2147 if opts = @opts[:insert_conflict] 2148 sql << " ON CONFLICT" 2149 2150 if target = opts[:constraint] 2151 sql << " ON CONSTRAINT " 2152 identifier_append(sql, target) 2153 elsif target = opts[:target] 2154 sql << ' ' 2155 identifier_append(sql, Array(target)) 2156 if conflict_where = opts[:conflict_where] 2157 sql << " WHERE " 2158 literal_append(sql, conflict_where) 2159 end 2160 end 2161 2162 if values = opts[:update] 2163 sql << " DO UPDATE SET " 2164 update_sql_values_hash(sql, values) 2165 if update_where = opts[:update_where] 2166 sql << " WHERE " 2167 literal_append(sql, update_where) 2168 end 2169 else 2170 sql << " DO NOTHING" 2171 end 2172 end 2173 end
Include aliases when inserting into a single table on PostgreSQL 9.5+.
# File lib/sequel/adapters/shared/postgres.rb 2176 def insert_into_sql(sql) 2177 sql << " INTO " 2178 if (f = @opts[:from]) && f.length == 1 2179 identifier_append(sql, server_version >= 90500 ? f.first : unaliased_identifier(f.first)) 2180 else 2181 source_list_append(sql, f) 2182 end 2183 end
Support OVERRIDING SYSTEM|USER VALUE in insert statements
# File lib/sequel/adapters/shared/postgres.rb 2197 def insert_override_sql(sql) 2198 case opts[:override] 2199 when :system 2200 sql << " OVERRIDING SYSTEM VALUE" 2201 when :user 2202 sql << " OVERRIDING USER VALUE" 2203 end 2204 end
Return the primary key to use for RETURNING in an INSERT statement
# File lib/sequel/adapters/shared/postgres.rb 2186 def insert_pk 2187 (f = opts[:from]) && !f.empty? && (t = f.first) 2188 case t 2189 when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier 2190 if pk = db.primary_key(t) 2191 Sequel::SQL::Identifier.new(pk) 2192 end 2193 end 2194 end
For multiple table support, PostgreSQL requires at least two from tables, with joins allowed.
# File lib/sequel/adapters/shared/postgres.rb 2208 def join_from_sql(type, sql) 2209 if(from = @opts[:from][1..-1]).empty? 2210 raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join] 2211 else 2212 sql << ' ' << type.to_s << ' ' 2213 source_list_append(sql, from) 2214 select_join_sql(sql) 2215 end 2216 end
Support table aliases for USING columns
# File lib/sequel/adapters/shared/postgres.rb 2219 def join_using_clause_using_sql_append(sql, using_columns) 2220 if using_columns.is_a?(SQL::AliasedExpression) 2221 super(sql, using_columns.expression) 2222 sql << ' AS ' 2223 identifier_append(sql, using_columns.alias) 2224 else 2225 super 2226 end 2227 end
Use a generic blob quoting method, hopefully overridden in one of the subadapter methods
# File lib/sequel/adapters/shared/postgres.rb 2230 def literal_blob_append(sql, v) 2231 sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'" 2232 end
PostgreSQL uses FALSE for false values
# File lib/sequel/adapters/shared/postgres.rb 2235 def literal_false 2236 'false' 2237 end
PostgreSQL quotes NaN and Infinity.
# File lib/sequel/adapters/shared/postgres.rb 2240 def literal_float(value) 2241 if value.finite? 2242 super 2243 elsif value.nan? 2244 "'NaN'" 2245 elsif value.infinite? == 1 2246 "'Infinity'" 2247 else 2248 "'-Infinity'" 2249 end 2250 end
Handle Ruby integers outside PostgreSQL bigint range specially.
# File lib/sequel/adapters/shared/postgres.rb 2253 def literal_integer(v) 2254 if v > 9223372036854775807 || v < -9223372036854775808 2255 literal_integer_outside_bigint_range(v) 2256 else 2257 v.to_s 2258 end 2259 end
Raise IntegerOutsideBigintRange
when attempting to literalize Ruby integer outside PostgreSQL bigint range, so PostgreSQL doesn't treat the value as numeric.
# File lib/sequel/adapters/shared/postgres.rb 2264 def literal_integer_outside_bigint_range(v) 2265 raise IntegerOutsideBigintRange, "attempt to literalize Ruby integer outside PostgreSQL bigint range: #{v}" 2266 end
Assume that SQL
standard quoting is on, per Sequel's defaults
# File lib/sequel/adapters/shared/postgres.rb 2269 def literal_string_append(sql, v) 2270 sql << "'" << v.gsub("'", "''") << "'" 2271 end
PostgreSQL uses true for true values
# File lib/sequel/adapters/shared/postgres.rb 2274 def literal_true 2275 'true' 2276 end
PostgreSQL supports multiple rows in INSERT.
# File lib/sequel/adapters/shared/postgres.rb 2279 def multi_insert_sql_strategy 2280 :values 2281 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 2298 def requires_like_escape? 2299 false 2300 end
Support FETCH FIRST WITH TIES on PostgreSQL 13+.
# File lib/sequel/adapters/shared/postgres.rb 2303 def select_limit_sql(sql) 2304 l = @opts[:limit] 2305 o = @opts[:offset] 2306 2307 return unless l || o 2308 2309 if @opts[:limit_with_ties] 2310 if o 2311 sql << " OFFSET " 2312 literal_append(sql, o) 2313 end 2314 2315 if l 2316 sql << " FETCH FIRST " 2317 literal_append(sql, l) 2318 sql << " ROWS WITH TIES" 2319 end 2320 else 2321 if l 2322 sql << " LIMIT " 2323 literal_append(sql, l) 2324 end 2325 2326 if o 2327 sql << " OFFSET " 2328 literal_append(sql, o) 2329 end 2330 end 2331 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 2335 def select_lock_sql(sql) 2336 lock = @opts[:lock] 2337 if lock == :share 2338 sql << ' FOR SHARE' 2339 else 2340 super 2341 end 2342 2343 if lock 2344 if @opts[:skip_locked] 2345 sql << " SKIP LOCKED" 2346 elsif @opts[:nowait] 2347 sql << " NOWAIT" 2348 end 2349 end 2350 end
Support VALUES clause instead of the SELECT clause to return rows.
# File lib/sequel/adapters/shared/postgres.rb 2353 def select_values_sql(sql) 2354 sql << "VALUES " 2355 expression_list_append(sql, opts[:values]) 2356 end
Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive
# File lib/sequel/adapters/shared/postgres.rb 2359 def select_with_sql_base 2360 opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super 2361 end
Support PostgreSQL 14+ CTE SEARCH/CYCLE clauses
# File lib/sequel/adapters/shared/postgres.rb 2364 def select_with_sql_cte(sql, cte) 2365 super 2366 select_with_sql_cte_search_cycle(sql, cte) 2367 end
# File lib/sequel/adapters/shared/postgres.rb 2369 def select_with_sql_cte_search_cycle(sql, cte) 2370 if search_opts = cte[:search] 2371 sql << if search_opts[:type] == :breadth 2372 " SEARCH BREADTH FIRST BY " 2373 else 2374 " SEARCH DEPTH FIRST BY " 2375 end 2376 2377 identifier_list_append(sql, Array(search_opts[:by])) 2378 sql << " SET " 2379 identifier_append(sql, search_opts[:set] || :ordercol) 2380 end 2381 2382 if cycle_opts = cte[:cycle] 2383 sql << " CYCLE " 2384 identifier_list_append(sql, Array(cycle_opts[:columns])) 2385 sql << " SET " 2386 identifier_append(sql, cycle_opts[:cycle_column] || :is_cycle) 2387 if cycle_opts.has_key?(:cycle_value) 2388 sql << " TO " 2389 literal_append(sql, cycle_opts[:cycle_value]) 2390 sql << " DEFAULT " 2391 literal_append(sql, cycle_opts.fetch(:noncycle_value, false)) 2392 end 2393 sql << " USING " 2394 identifier_append(sql, cycle_opts[:path_column] || :path) 2395 end 2396 end
The version of the database server
# File lib/sequel/adapters/shared/postgres.rb 2399 def server_version 2400 db.server_version(@opts[:server]) 2401 end
PostgreSQL 9.4+ supports the FILTER clause for aggregate functions.
# File lib/sequel/adapters/shared/postgres.rb 2404 def supports_filtered_aggregates? 2405 server_version >= 90400 2406 end
PostgreSQL supports quoted function names.
# File lib/sequel/adapters/shared/postgres.rb 2409 def supports_quoted_function_names? 2410 true 2411 end
Use FROM to specify additional tables in an update query
# File lib/sequel/adapters/shared/postgres.rb 2422 def update_from_sql(sql) 2423 join_from_sql(:FROM, sql) 2424 end
Only include the primary table in the main update clause
# File lib/sequel/adapters/shared/postgres.rb 2427 def update_table_sql(sql) 2428 sql << ' ' 2429 source_list_append(sql, @opts[:from][0..0]) 2430 end