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 1726 def analyze 1727 explain(:analyze=>true) 1728 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 1733 def complex_expression_sql_append(sql, op, args) 1734 case op 1735 when :^ 1736 j = ' # ' 1737 c = false 1738 args.each do |a| 1739 sql << j if c 1740 literal_append(sql, a) 1741 c ||= true 1742 end 1743 when :ILIKE, :'NOT ILIKE' 1744 sql << '(' 1745 literal_append(sql, args[0]) 1746 sql << ' ' << op.to_s << ' ' 1747 literal_append(sql, args[1]) 1748 sql << ')' 1749 else 1750 super 1751 end 1752 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 1768 def disable_insert_returning 1769 clone(:disable_insert_returning=>true) 1770 end
Always return false when using VALUES
# File lib/sequel/adapters/shared/postgres.rb 1773 def empty? 1774 return false if @opts[:values] 1775 super 1776 end
Return the results of an EXPLAIN query as a string
# File lib/sequel/adapters/shared/postgres.rb 1779 def explain(opts=OPTS) 1780 with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map(:'QUERY PLAN').join("\r\n") 1781 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, :phrase, or :websearch 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 1807 def full_text_search(cols, terms, opts = OPTS) 1808 lang = Sequel.cast(opts[:language] || 'simple', :regconfig) 1809 1810 unless opts[:tsvector] 1811 phrase_cols = full_text_string_join(cols) 1812 cols = Sequel.function(:to_tsvector, lang, phrase_cols) 1813 end 1814 1815 unless opts[:tsquery] 1816 phrase_terms = terms.is_a?(Array) ? terms.join(' | ') : terms 1817 1818 query_func = case to_tsquery = opts[:to_tsquery] 1819 when :phrase, :plain 1820 :"#{to_tsquery}to_tsquery" 1821 when :websearch 1822 :"websearch_to_tsquery" 1823 else 1824 (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery 1825 end 1826 1827 terms = Sequel.function(query_func, lang, phrase_terms) 1828 end 1829 1830 ds = where(Sequel.lit(["", " @@ ", ""], cols, terms)) 1831 1832 if opts[:phrase] 1833 raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery] 1834 ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true) 1835 end 1836 1837 if opts[:rank] 1838 ds = ds.reverse{ts_rank_cd(cols, terms)} 1839 end 1840 1841 if opts[:headline] 1842 ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)} 1843 end 1844 1845 ds 1846 end
Insert given values into the database.
# File lib/sequel/adapters/shared/postgres.rb 1849 def insert(*values) 1850 if @opts[:returning] 1851 # Already know which columns to return, let the standard code handle it 1852 super 1853 elsif @opts[:sql] || @opts[:disable_insert_returning] 1854 # Raw SQL used or RETURNING disabled, just use the default behavior 1855 # and return nil since sequence is not known. 1856 super 1857 nil 1858 else 1859 # Force the use of RETURNING with the primary key value, 1860 # unless it has been disabled. 1861 returning(insert_pk).insert(*values){|r| return r.values.first} 1862 end 1863 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 1900 def insert_conflict(opts=OPTS) 1901 clone(:insert_conflict => opts) 1902 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 1910 def insert_ignore 1911 insert_conflict 1912 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 1917 def insert_select(*values) 1918 return unless supports_insert_select? 1919 # Handle case where query does not return a row 1920 server?(:default).with_sql_first(insert_select_sql(*values)) || false 1921 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 1925 def insert_select_sql(*values) 1926 ds = opts[:returning] ? self : returning 1927 ds.insert_sql(*values) 1928 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 1932 def join_table(type, table, expr=nil, options=OPTS, &block) 1933 if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all? 1934 options = options.merge(:join_using=>true) 1935 end 1936 super 1937 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 1944 def lock(mode, opts=OPTS) 1945 if defined?(yield) # perform locking inside a transaction and yield to block 1946 @db.transaction(opts){lock(mode, opts); yield} 1947 else 1948 sql = 'LOCK TABLE '.dup 1949 source_list_append(sql, @opts[:from]) 1950 mode = mode.to_s.upcase.strip 1951 unless LOCK_MODES.include?(mode) 1952 raise Error, "Unsupported lock mode: #{mode}" 1953 end 1954 sql << " IN #{mode} MODE" 1955 @db.execute(sql, opts) 1956 end 1957 nil 1958 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 1969 def merge_do_nothing_when_matched(&block) 1970 _merge_when(:type=>:matched, &block) 1971 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 1982 def merge_do_nothing_when_not_matched(&block) 1983 _merge_when(:type=>:not_matched, &block) 1984 end
Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.
# File lib/sequel/adapters/shared/postgres.rb 1987 def merge_insert(*values, &block) 1988 h = {:type=>:insert, :values=>values} 1989 if override = @opts[:override] 1990 h[:override] = insert_override_sql(String.new) 1991 end 1992 _merge_when(h, &block) 1993 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 1998 def overriding_system_value 1999 clone(:override=>:system) 2000 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 2004 def overriding_user_value 2005 clone(:override=>:user) 2006 end
# File lib/sequel/adapters/shared/postgres.rb 2008 def supports_cte?(type=:select) 2009 if type == :select 2010 server_version >= 80400 2011 else 2012 server_version >= 90100 2013 end 2014 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 2018 def supports_cte_in_subqueries? 2019 supports_cte? 2020 end
DISTINCT ON is a PostgreSQL extension
# File lib/sequel/adapters/shared/postgres.rb 2023 def supports_distinct_on? 2024 true 2025 end
PostgreSQL 9.5+ supports GROUP CUBE
# File lib/sequel/adapters/shared/postgres.rb 2028 def supports_group_cube? 2029 server_version >= 90500 2030 end
PostgreSQL 9.5+ supports GROUP ROLLUP
# File lib/sequel/adapters/shared/postgres.rb 2033 def supports_group_rollup? 2034 server_version >= 90500 2035 end
PostgreSQL 9.5+ supports GROUPING SETS
# File lib/sequel/adapters/shared/postgres.rb 2038 def supports_grouping_sets? 2039 server_version >= 90500 2040 end
PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.
# File lib/sequel/adapters/shared/postgres.rb 2048 def supports_insert_conflict? 2049 server_version >= 90500 2050 end
True unless insert returning has been disabled for this dataset.
# File lib/sequel/adapters/shared/postgres.rb 2043 def supports_insert_select? 2044 !@opts[:disable_insert_returning] 2045 end
PostgreSQL 9.3+ supports lateral subqueries
# File lib/sequel/adapters/shared/postgres.rb 2053 def supports_lateral_subqueries? 2054 server_version >= 90300 2055 end
PostgreSQL 15+ supports MERGE.
# File lib/sequel/adapters/shared/postgres.rb 2063 def supports_merge? 2064 server_version >= 150000 2065 end
PostgreSQL supports modifying joined datasets
# File lib/sequel/adapters/shared/postgres.rb 2058 def supports_modifying_joins? 2059 true 2060 end
PostgreSQL supports NOWAIT.
# File lib/sequel/adapters/shared/postgres.rb 2068 def supports_nowait? 2069 true 2070 end
PostgreSQL supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/postgres.rb 2078 def supports_regexp? 2079 true 2080 end
Returning is always supported.
# File lib/sequel/adapters/shared/postgres.rb 2073 def supports_returning?(type) 2074 true 2075 end
PostgreSQL 9.5+ supports SKIP LOCKED.
# File lib/sequel/adapters/shared/postgres.rb 2083 def supports_skip_locked? 2084 server_version >= 90500 2085 end
PostgreSQL supports timezones in literal timestamps
# File lib/sequel/adapters/shared/postgres.rb 2088 def supports_timestamp_timezones? 2089 true 2090 end
PostgreSQL 8.4+ supports WINDOW clause.
# File lib/sequel/adapters/shared/postgres.rb 2093 def supports_window_clause? 2094 server_version >= 80400 2095 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 2104 def supports_window_function_frame_option?(option) 2105 case option 2106 when :rows, :range 2107 true 2108 when :offset 2109 server_version >= 90000 2110 when :groups, :exclude 2111 server_version >= 110000 2112 else 2113 false 2114 end 2115 end
PostgreSQL 8.4+ supports window functions
# File lib/sequel/adapters/shared/postgres.rb 2098 def supports_window_functions? 2099 server_version >= 80400 2100 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 2133 def truncate(opts = OPTS) 2134 if opts.empty? 2135 super() 2136 else 2137 clone(:truncate_opts=>opts).truncate 2138 end 2139 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 2144 def with_ties 2145 clone(:limit_with_ties=>true) 2146 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 2154 def _import(columns, values, opts=OPTS) 2155 if @opts[:returning] 2156 # no transaction: our multi_insert_sql_strategy should guarantee 2157 # that there's only ever a single statement. 2158 sql = multi_insert_sql(columns, values)[0] 2159 returning_fetch_rows(sql).map{|v| v.length == 1 ? v.values.first : v} 2160 elsif opts[:return] == :primary_key 2161 returning(insert_pk)._import(columns, values, opts) 2162 else 2163 super 2164 end 2165 end
# File lib/sequel/adapters/shared/postgres.rb 2167 def to_prepared_statement(type, *a) 2168 if type == :insert && !@opts.has_key?(:returning) 2169 returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a) 2170 else 2171 super 2172 end 2173 end
Private Instance Methods
Append the INSERT sql used in a MERGE
# File lib/sequel/adapters/shared/postgres.rb 2178 def _merge_insert_sql(sql, data) 2179 sql << " THEN INSERT " 2180 columns, values = _parse_insert_sql_args(data[:values]) 2181 _insert_columns_sql(sql, columns) 2182 if override = data[:override] 2183 sql << override 2184 end 2185 _insert_values_sql(sql, values) 2186 end
# File lib/sequel/adapters/shared/postgres.rb 2188 def _merge_matched_sql(sql, data) 2189 sql << " THEN DO NOTHING" 2190 end
Format TRUNCATE statement with PostgreSQL specific options.
# File lib/sequel/adapters/shared/postgres.rb 2194 def _truncate_sql(table) 2195 to = @opts[:truncate_opts] || OPTS 2196 "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}" 2197 end
Use from_self for aggregate dataset using VALUES.
# File lib/sequel/adapters/shared/postgres.rb 2200 def aggreate_dataset_use_from_self? 2201 super || @opts[:values] 2202 end
Allow truncation of multiple source tables.
# File lib/sequel/adapters/shared/postgres.rb 2205 def check_truncation_allowed! 2206 raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group] 2207 raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join] 2208 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 2366 def compound_dataset_sql_append(sql, ds) 2367 sql << '(' 2368 super 2369 sql << ')' 2370 end
Only include the primary table in the main delete clause
# File lib/sequel/adapters/shared/postgres.rb 2211 def delete_from_sql(sql) 2212 sql << ' FROM ' 2213 source_list_append(sql, @opts[:from][0..0]) 2214 end
Use USING to specify additional tables in a delete query
# File lib/sequel/adapters/shared/postgres.rb 2217 def delete_using_sql(sql) 2218 join_from_sql(:USING, sql) 2219 end
Concatenate the expressions with a space in between
# File lib/sequel/adapters/shared/postgres.rb 2490 def full_text_string_join(cols) 2491 cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')} 2492 cols = cols.zip([' '] * cols.length).flatten 2493 cols.pop 2494 SQL::StringExpression.new(:'||', *cols) 2495 end
Add ON CONFLICT clause if it should be used
# File lib/sequel/adapters/shared/postgres.rb 2222 def insert_conflict_sql(sql) 2223 if opts = @opts[:insert_conflict] 2224 sql << " ON CONFLICT" 2225 2226 if target = opts[:constraint] 2227 sql << " ON CONSTRAINT " 2228 identifier_append(sql, target) 2229 elsif target = opts[:target] 2230 sql << ' ' 2231 identifier_append(sql, Array(target)) 2232 if conflict_where = opts[:conflict_where] 2233 sql << " WHERE " 2234 literal_append(sql, conflict_where) 2235 end 2236 end 2237 2238 if values = opts[:update] 2239 sql << " DO UPDATE SET " 2240 update_sql_values_hash(sql, values) 2241 if update_where = opts[:update_where] 2242 sql << " WHERE " 2243 literal_append(sql, update_where) 2244 end 2245 else 2246 sql << " DO NOTHING" 2247 end 2248 end 2249 end
Include aliases when inserting into a single table on PostgreSQL 9.5+.
# File lib/sequel/adapters/shared/postgres.rb 2252 def insert_into_sql(sql) 2253 sql << " INTO " 2254 if (f = @opts[:from]) && f.length == 1 2255 identifier_append(sql, server_version >= 90500 ? f.first : unaliased_identifier(f.first)) 2256 else 2257 source_list_append(sql, f) 2258 end 2259 end
Support OVERRIDING SYSTEM|USER VALUE in insert statements
# File lib/sequel/adapters/shared/postgres.rb 2273 def insert_override_sql(sql) 2274 case opts[:override] 2275 when :system 2276 sql << " OVERRIDING SYSTEM VALUE" 2277 when :user 2278 sql << " OVERRIDING USER VALUE" 2279 end 2280 end
Return the primary key to use for RETURNING in an INSERT statement
# File lib/sequel/adapters/shared/postgres.rb 2262 def insert_pk 2263 (f = opts[:from]) && !f.empty? && (t = f.first) 2264 case t 2265 when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier 2266 if pk = db.primary_key(t) 2267 Sequel::SQL::Identifier.new(pk) 2268 end 2269 end 2270 end
For multiple table support, PostgreSQL requires at least two from tables, with joins allowed.
# File lib/sequel/adapters/shared/postgres.rb 2284 def join_from_sql(type, sql) 2285 if(from = @opts[:from][1..-1]).empty? 2286 raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join] 2287 else 2288 sql << ' ' << type.to_s << ' ' 2289 source_list_append(sql, from) 2290 select_join_sql(sql) 2291 end 2292 end
Support table aliases for USING columns
# File lib/sequel/adapters/shared/postgres.rb 2295 def join_using_clause_using_sql_append(sql, using_columns) 2296 if using_columns.is_a?(SQL::AliasedExpression) 2297 super(sql, using_columns.expression) 2298 sql << ' AS ' 2299 identifier_append(sql, using_columns.alias) 2300 else 2301 super 2302 end 2303 end
Use a generic blob quoting method, hopefully overridden in one of the subadapter methods
# File lib/sequel/adapters/shared/postgres.rb 2306 def literal_blob_append(sql, v) 2307 sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'" 2308 end
PostgreSQL uses FALSE for false values
# File lib/sequel/adapters/shared/postgres.rb 2311 def literal_false 2312 'false' 2313 end
PostgreSQL quotes NaN and Infinity.
# File lib/sequel/adapters/shared/postgres.rb 2316 def literal_float(value) 2317 if value.finite? 2318 super 2319 elsif value.nan? 2320 "'NaN'" 2321 elsif value.infinite? == 1 2322 "'Infinity'" 2323 else 2324 "'-Infinity'" 2325 end 2326 end
Handle Ruby integers outside PostgreSQL bigint range specially.
# File lib/sequel/adapters/shared/postgres.rb 2329 def literal_integer(v) 2330 if v > 9223372036854775807 || v < -9223372036854775808 2331 literal_integer_outside_bigint_range(v) 2332 else 2333 v.to_s 2334 end 2335 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 2340 def literal_integer_outside_bigint_range(v) 2341 raise IntegerOutsideBigintRange, "attempt to literalize Ruby integer outside PostgreSQL bigint range: #{v}" 2342 end
Assume that SQL
standard quoting is on, per Sequel's defaults
# File lib/sequel/adapters/shared/postgres.rb 2345 def literal_string_append(sql, v) 2346 sql << "'" << v.gsub("'", "''") << "'" 2347 end
PostgreSQL uses true for true values
# File lib/sequel/adapters/shared/postgres.rb 2350 def literal_true 2351 'true' 2352 end
PostgreSQL supports multiple rows in INSERT.
# File lib/sequel/adapters/shared/postgres.rb 2355 def multi_insert_sql_strategy 2356 :values 2357 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 2374 def requires_like_escape? 2375 false 2376 end
Support FETCH FIRST WITH TIES on PostgreSQL 13+.
# File lib/sequel/adapters/shared/postgres.rb 2379 def select_limit_sql(sql) 2380 l = @opts[:limit] 2381 o = @opts[:offset] 2382 2383 return unless l || o 2384 2385 if @opts[:limit_with_ties] 2386 if o 2387 sql << " OFFSET " 2388 literal_append(sql, o) 2389 end 2390 2391 if l 2392 sql << " FETCH FIRST " 2393 literal_append(sql, l) 2394 sql << " ROWS WITH TIES" 2395 end 2396 else 2397 if l 2398 sql << " LIMIT " 2399 literal_append(sql, l) 2400 end 2401 2402 if o 2403 sql << " OFFSET " 2404 literal_append(sql, o) 2405 end 2406 end 2407 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 2411 def select_lock_sql(sql) 2412 lock = @opts[:lock] 2413 if lock == :share 2414 sql << ' FOR SHARE' 2415 else 2416 super 2417 end 2418 2419 if lock 2420 if @opts[:skip_locked] 2421 sql << " SKIP LOCKED" 2422 elsif @opts[:nowait] 2423 sql << " NOWAIT" 2424 end 2425 end 2426 end
Support VALUES clause instead of the SELECT clause to return rows.
# File lib/sequel/adapters/shared/postgres.rb 2429 def select_values_sql(sql) 2430 sql << "VALUES " 2431 expression_list_append(sql, opts[:values]) 2432 end
Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive
# File lib/sequel/adapters/shared/postgres.rb 2435 def select_with_sql_base 2436 opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super 2437 end
Support PostgreSQL 14+ CTE SEARCH/CYCLE clauses
# File lib/sequel/adapters/shared/postgres.rb 2440 def select_with_sql_cte(sql, cte) 2441 super 2442 select_with_sql_cte_search_cycle(sql, cte) 2443 end
# File lib/sequel/adapters/shared/postgres.rb 2445 def select_with_sql_cte_search_cycle(sql, cte) 2446 if search_opts = cte[:search] 2447 sql << if search_opts[:type] == :breadth 2448 " SEARCH BREADTH FIRST BY " 2449 else 2450 " SEARCH DEPTH FIRST BY " 2451 end 2452 2453 identifier_list_append(sql, Array(search_opts[:by])) 2454 sql << " SET " 2455 identifier_append(sql, search_opts[:set] || :ordercol) 2456 end 2457 2458 if cycle_opts = cte[:cycle] 2459 sql << " CYCLE " 2460 identifier_list_append(sql, Array(cycle_opts[:columns])) 2461 sql << " SET " 2462 identifier_append(sql, cycle_opts[:cycle_column] || :is_cycle) 2463 if cycle_opts.has_key?(:cycle_value) 2464 sql << " TO " 2465 literal_append(sql, cycle_opts[:cycle_value]) 2466 sql << " DEFAULT " 2467 literal_append(sql, cycle_opts.fetch(:noncycle_value, false)) 2468 end 2469 sql << " USING " 2470 identifier_append(sql, cycle_opts[:path_column] || :path) 2471 end 2472 end
The version of the database server
# File lib/sequel/adapters/shared/postgres.rb 2475 def server_version 2476 db.server_version(@opts[:server]) 2477 end
PostgreSQL 9.4+ supports the FILTER clause for aggregate functions.
# File lib/sequel/adapters/shared/postgres.rb 2480 def supports_filtered_aggregates? 2481 server_version >= 90400 2482 end
PostgreSQL supports quoted function names.
# File lib/sequel/adapters/shared/postgres.rb 2485 def supports_quoted_function_names? 2486 true 2487 end
Use FROM to specify additional tables in an update query
# File lib/sequel/adapters/shared/postgres.rb 2498 def update_from_sql(sql) 2499 join_from_sql(:FROM, sql) 2500 end
Only include the primary table in the main update clause
# File lib/sequel/adapters/shared/postgres.rb 2503 def update_table_sql(sql) 2504 sql << ' ' 2505 source_list_append(sql, @opts[:from][0..0]) 2506 end