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 1533 def analyze 1534 explain(:analyze=>true) 1535 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 1540 def complex_expression_sql_append(sql, op, args) 1541 case op 1542 when :^ 1543 j = ' # ' 1544 c = false 1545 args.each do |a| 1546 sql << j if c 1547 literal_append(sql, a) 1548 c ||= true 1549 end 1550 when :ILIKE, :'NOT ILIKE' 1551 sql << '(' 1552 literal_append(sql, args[0]) 1553 sql << ' ' << op.to_s << ' ' 1554 literal_append(sql, args[1]) 1555 sql << " ESCAPE " 1556 literal_append(sql, "\\") 1557 sql << ')' 1558 else 1559 super 1560 end 1561 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 1577 def disable_insert_returning 1578 clone(:disable_insert_returning=>true) 1579 end
Return the results of an EXPLAIN query as a string
# File lib/sequel/adapters/shared/postgres.rb 1582 def explain(opts=OPTS) 1583 with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map(:'QUERY PLAN').join("\r\n") 1584 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 1610 def full_text_search(cols, terms, opts = OPTS) 1611 lang = Sequel.cast(opts[:language] || 'simple', :regconfig) 1612 1613 unless opts[:tsvector] 1614 phrase_cols = full_text_string_join(cols) 1615 cols = Sequel.function(:to_tsvector, lang, phrase_cols) 1616 end 1617 1618 unless opts[:tsquery] 1619 phrase_terms = terms.is_a?(Array) ? terms.join(' | ') : terms 1620 1621 query_func = case to_tsquery = opts[:to_tsquery] 1622 when :phrase, :plain 1623 :"#{to_tsquery}to_tsquery" 1624 else 1625 (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery 1626 end 1627 1628 terms = Sequel.function(query_func, lang, phrase_terms) 1629 end 1630 1631 ds = where(Sequel.lit(["", " @@ ", ""], cols, terms)) 1632 1633 if opts[:phrase] 1634 raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery] 1635 ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true) 1636 end 1637 1638 if opts[:rank] 1639 ds = ds.reverse{ts_rank_cd(cols, terms)} 1640 end 1641 1642 if opts[:headline] 1643 ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)} 1644 end 1645 1646 ds 1647 end
Insert given values into the database.
# File lib/sequel/adapters/shared/postgres.rb 1650 def insert(*values) 1651 if @opts[:returning] 1652 # Already know which columns to return, let the standard code handle it 1653 super 1654 elsif @opts[:sql] || @opts[:disable_insert_returning] 1655 # Raw SQL used or RETURNING disabled, just use the default behavior 1656 # and return nil since sequence is not known. 1657 super 1658 nil 1659 else 1660 # Force the use of RETURNING with the primary key value, 1661 # unless it has been disabled. 1662 returning(insert_pk).insert(*values){|r| return r.values.first} 1663 end 1664 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 1701 def insert_conflict(opts=OPTS) 1702 clone(:insert_conflict => opts) 1703 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 1711 def insert_ignore 1712 insert_conflict 1713 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 1718 def insert_select(*values) 1719 return unless supports_insert_select? 1720 # Handle case where query does not return a row 1721 server?(:default).with_sql_first(insert_select_sql(*values)) || false 1722 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 1726 def insert_select_sql(*values) 1727 ds = opts[:returning] ? self : returning 1728 ds.insert_sql(*values) 1729 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 1733 def join_table(type, table, expr=nil, options=OPTS, &block) 1734 if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all? 1735 options = options.merge(:join_using=>true) 1736 end 1737 super 1738 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 1745 def lock(mode, opts=OPTS) 1746 if defined?(yield) # perform locking inside a transaction and yield to block 1747 @db.transaction(opts){lock(mode, opts); yield} 1748 else 1749 sql = 'LOCK TABLE '.dup 1750 source_list_append(sql, @opts[:from]) 1751 mode = mode.to_s.upcase.strip 1752 unless LOCK_MODES.include?(mode) 1753 raise Error, "Unsupported lock mode: #{mode}" 1754 end 1755 sql << " IN #{mode} MODE" 1756 @db.execute(sql, opts) 1757 end 1758 nil 1759 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 1764 def overriding_system_value 1765 clone(:override=>:system) 1766 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 1770 def overriding_user_value 1771 clone(:override=>:user) 1772 end
# File lib/sequel/adapters/shared/postgres.rb 1774 def supports_cte?(type=:select) 1775 if type == :select 1776 server_version >= 80400 1777 else 1778 server_version >= 90100 1779 end 1780 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 1784 def supports_cte_in_subqueries? 1785 supports_cte? 1786 end
DISTINCT ON is a PostgreSQL extension
# File lib/sequel/adapters/shared/postgres.rb 1789 def supports_distinct_on? 1790 true 1791 end
PostgreSQL 9.5+ supports GROUP CUBE
# File lib/sequel/adapters/shared/postgres.rb 1794 def supports_group_cube? 1795 server_version >= 90500 1796 end
PostgreSQL 9.5+ supports GROUP ROLLUP
# File lib/sequel/adapters/shared/postgres.rb 1799 def supports_group_rollup? 1800 server_version >= 90500 1801 end
PostgreSQL 9.5+ supports GROUPING SETS
# File lib/sequel/adapters/shared/postgres.rb 1804 def supports_grouping_sets? 1805 server_version >= 90500 1806 end
PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.
# File lib/sequel/adapters/shared/postgres.rb 1814 def supports_insert_conflict? 1815 server_version >= 90500 1816 end
True unless insert returning has been disabled for this dataset.
# File lib/sequel/adapters/shared/postgres.rb 1809 def supports_insert_select? 1810 !@opts[:disable_insert_returning] 1811 end
PostgreSQL 9.3+ supports lateral subqueries
# File lib/sequel/adapters/shared/postgres.rb 1819 def supports_lateral_subqueries? 1820 server_version >= 90300 1821 end
PostgreSQL supports modifying joined datasets
# File lib/sequel/adapters/shared/postgres.rb 1824 def supports_modifying_joins? 1825 true 1826 end
PostgreSQL supports NOWAIT.
# File lib/sequel/adapters/shared/postgres.rb 1829 def supports_nowait? 1830 true 1831 end
PostgreSQL supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/postgres.rb 1839 def supports_regexp? 1840 true 1841 end
Returning is always supported.
# File lib/sequel/adapters/shared/postgres.rb 1834 def supports_returning?(type) 1835 true 1836 end
PostgreSQL 9.5+ supports SKIP LOCKED.
# File lib/sequel/adapters/shared/postgres.rb 1844 def supports_skip_locked? 1845 server_version >= 90500 1846 end
PostgreSQL supports timezones in literal timestamps
# File lib/sequel/adapters/shared/postgres.rb 1849 def supports_timestamp_timezones? 1850 true 1851 end
PostgreSQL 8.4+ supports WINDOW clause.
# File lib/sequel/adapters/shared/postgres.rb 1854 def supports_window_clause? 1855 server_version >= 80400 1856 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 1865 def supports_window_function_frame_option?(option) 1866 case option 1867 when :rows, :range 1868 true 1869 when :offset 1870 server_version >= 90000 1871 when :groups, :exclude 1872 server_version >= 110000 1873 end 1874 end
PostgreSQL 8.4+ supports window functions
# File lib/sequel/adapters/shared/postgres.rb 1859 def supports_window_functions? 1860 server_version >= 80400 1861 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 1892 def truncate(opts = OPTS) 1893 if opts.empty? 1894 super() 1895 else 1896 clone(:truncate_opts=>opts).truncate 1897 end 1898 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 1903 def with_ties 1904 clone(:limit_with_ties=>true) 1905 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 1913 def _import(columns, values, opts=OPTS) 1914 if @opts[:returning] 1915 statements = multi_insert_sql(columns, values) 1916 trans_opts = Hash[opts] 1917 trans_opts[:server] = @opts[:server] 1918 @db.transaction(trans_opts) do 1919 statements.map{|st| returning_fetch_rows(st)} 1920 end.first.map{|v| v.length == 1 ? v.values.first : v} 1921 elsif opts[:return] == :primary_key 1922 returning(insert_pk)._import(columns, values, opts) 1923 else 1924 super 1925 end 1926 end
# File lib/sequel/adapters/shared/postgres.rb 1928 def to_prepared_statement(type, *a) 1929 if type == :insert && !@opts.has_key?(:returning) 1930 returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a) 1931 else 1932 super 1933 end 1934 end
Private Instance Methods
Format TRUNCATE statement with PostgreSQL specific options.
# File lib/sequel/adapters/shared/postgres.rb 1939 def _truncate_sql(table) 1940 to = @opts[:truncate_opts] || OPTS 1941 "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}" 1942 end
Allow truncation of multiple source tables.
# File lib/sequel/adapters/shared/postgres.rb 1945 def check_truncation_allowed! 1946 raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group] 1947 raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join] 1948 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 2092 def compound_dataset_sql_append(sql, ds) 2093 sql << '(' 2094 super 2095 sql << ')' 2096 end
Only include the primary table in the main delete clause
# File lib/sequel/adapters/shared/postgres.rb 1951 def delete_from_sql(sql) 1952 sql << ' FROM ' 1953 source_list_append(sql, @opts[:from][0..0]) 1954 end
Use USING to specify additional tables in a delete query
# File lib/sequel/adapters/shared/postgres.rb 1957 def delete_using_sql(sql) 1958 join_from_sql(:USING, sql) 1959 end
Concatenate the expressions with a space in between
# File lib/sequel/adapters/shared/postgres.rb 2213 def full_text_string_join(cols) 2214 cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')} 2215 cols = cols.zip([' '] * cols.length).flatten 2216 cols.pop 2217 SQL::StringExpression.new(:'||', *cols) 2218 end
Add ON CONFLICT clause if it should be used
# File lib/sequel/adapters/shared/postgres.rb 1962 def insert_conflict_sql(sql) 1963 if opts = @opts[:insert_conflict] 1964 sql << " ON CONFLICT" 1965 1966 if target = opts[:constraint] 1967 sql << " ON CONSTRAINT " 1968 identifier_append(sql, target) 1969 elsif target = opts[:target] 1970 sql << ' ' 1971 identifier_append(sql, Array(target)) 1972 if conflict_where = opts[:conflict_where] 1973 sql << " WHERE " 1974 literal_append(sql, conflict_where) 1975 end 1976 end 1977 1978 if values = opts[:update] 1979 sql << " DO UPDATE SET " 1980 update_sql_values_hash(sql, values) 1981 if update_where = opts[:update_where] 1982 sql << " WHERE " 1983 literal_append(sql, update_where) 1984 end 1985 else 1986 sql << " DO NOTHING" 1987 end 1988 end 1989 end
Include aliases when inserting into a single table on PostgreSQL 9.5+.
# File lib/sequel/adapters/shared/postgres.rb 1992 def insert_into_sql(sql) 1993 sql << " INTO " 1994 if (f = @opts[:from]) && f.length == 1 1995 identifier_append(sql, server_version >= 90500 ? f.first : unaliased_identifier(f.first)) 1996 else 1997 source_list_append(sql, f) 1998 end 1999 end
Return the primary key to use for RETURNING in an INSERT statement
# File lib/sequel/adapters/shared/postgres.rb 2002 def insert_pk 2003 if (f = opts[:from]) && !f.empty? 2004 case t = f.first 2005 when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier 2006 if pk = db.primary_key(t) 2007 Sequel::SQL::Identifier.new(pk) 2008 end 2009 end 2010 end 2011 end
Support OVERRIDING SYSTEM|USER VALUE in insert statements
# File lib/sequel/adapters/shared/postgres.rb 2014 def insert_values_sql(sql) 2015 case opts[:override] 2016 when :system 2017 sql << " OVERRIDING SYSTEM VALUE" 2018 when :user 2019 sql << " OVERRIDING USER VALUE" 2020 end 2021 super 2022 end
For multiple table support, PostgreSQL requires at least two from tables, with joins allowed.
# File lib/sequel/adapters/shared/postgres.rb 2026 def join_from_sql(type, sql) 2027 if(from = @opts[:from][1..-1]).empty? 2028 raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join] 2029 else 2030 sql << ' ' << type.to_s << ' ' 2031 source_list_append(sql, from) 2032 select_join_sql(sql) 2033 end 2034 end
Support table aliases for USING columns
# File lib/sequel/adapters/shared/postgres.rb 2037 def join_using_clause_using_sql_append(sql, using_columns) 2038 if using_columns.is_a?(SQL::AliasedExpression) 2039 super(sql, using_columns.expression) 2040 sql << ' AS ' 2041 identifier_append(sql, using_columns.alias) 2042 else 2043 super 2044 end 2045 end
Use a generic blob quoting method, hopefully overridden in one of the subadapter methods
# File lib/sequel/adapters/shared/postgres.rb 2048 def literal_blob_append(sql, v) 2049 sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'" 2050 end
PostgreSQL uses FALSE for false values
# File lib/sequel/adapters/shared/postgres.rb 2053 def literal_false 2054 'false' 2055 end
PostgreSQL quotes NaN and Infinity.
# File lib/sequel/adapters/shared/postgres.rb 2058 def literal_float(value) 2059 if value.finite? 2060 super 2061 elsif value.nan? 2062 "'NaN'" 2063 elsif value.infinite? == 1 2064 "'Infinity'" 2065 else 2066 "'-Infinity'" 2067 end 2068 end
Assume that SQL
standard quoting is on, per Sequel's defaults
# File lib/sequel/adapters/shared/postgres.rb 2071 def literal_string_append(sql, v) 2072 sql << "'" << v.gsub("'", "''") << "'" 2073 end
PostgreSQL uses true for true values
# File lib/sequel/adapters/shared/postgres.rb 2076 def literal_true 2077 'true' 2078 end
PostgreSQL supports multiple rows in INSERT.
# File lib/sequel/adapters/shared/postgres.rb 2081 def multi_insert_sql_strategy 2082 :values 2083 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 2100 def requires_like_escape? 2101 false 2102 end
Support FETCH FIRST WITH TIES on PostgreSQL 13+.
# File lib/sequel/adapters/shared/postgres.rb 2105 def select_limit_sql(sql) 2106 l = @opts[:limit] 2107 o = @opts[:offset] 2108 2109 return unless l || o 2110 2111 if @opts[:limit_with_ties] 2112 if o 2113 sql << " OFFSET " 2114 literal_append(sql, o) 2115 end 2116 2117 if l 2118 sql << " FETCH FIRST " 2119 literal_append(sql, l) 2120 sql << " ROWS WITH TIES" 2121 end 2122 else 2123 if l 2124 sql << " LIMIT " 2125 literal_append(sql, l) 2126 end 2127 2128 if o 2129 sql << " OFFSET " 2130 literal_append(sql, o) 2131 end 2132 end 2133 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 2137 def select_lock_sql(sql) 2138 lock = @opts[:lock] 2139 if lock == :share 2140 sql << ' FOR SHARE' 2141 else 2142 super 2143 end 2144 2145 if lock 2146 if @opts[:skip_locked] 2147 sql << " SKIP LOCKED" 2148 elsif @opts[:nowait] 2149 sql << " NOWAIT" 2150 end 2151 end 2152 end
Support VALUES clause instead of the SELECT clause to return rows.
# File lib/sequel/adapters/shared/postgres.rb 2155 def select_values_sql(sql) 2156 sql << "VALUES " 2157 expression_list_append(sql, opts[:values]) 2158 end
Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive
# File lib/sequel/adapters/shared/postgres.rb 2161 def select_with_sql_base 2162 opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super 2163 end
Support PostgreSQL 14+ CTE SEARCH/CYCLE clauses
# File lib/sequel/adapters/shared/postgres.rb 2166 def select_with_sql_cte(sql, cte) 2167 super 2168 2169 if search_opts = cte[:search] 2170 sql << if search_opts[:type] == :breadth 2171 " SEARCH BREADTH FIRST BY " 2172 else 2173 " SEARCH DEPTH FIRST BY " 2174 end 2175 2176 identifier_list_append(sql, Array(search_opts[:by])) 2177 sql << " SET " 2178 identifier_append(sql, search_opts[:set] || :ordercol) 2179 end 2180 2181 if cycle_opts = cte[:cycle] 2182 sql << " CYCLE " 2183 identifier_list_append(sql, Array(cycle_opts[:columns])) 2184 sql << " SET " 2185 identifier_append(sql, cycle_opts[:cycle_column] || :is_cycle) 2186 if cycle_opts.has_key?(:cycle_value) 2187 sql << " TO " 2188 literal_append(sql, cycle_opts[:cycle_value]) 2189 sql << " DEFAULT " 2190 literal_append(sql, cycle_opts.fetch(:noncycle_value, false)) 2191 end 2192 sql << " USING " 2193 identifier_append(sql, cycle_opts[:path_column] || :path) 2194 end 2195 end
The version of the database server
# File lib/sequel/adapters/shared/postgres.rb 2198 def server_version 2199 db.server_version(@opts[:server]) 2200 end
PostgreSQL 9.4+ supports the FILTER clause for aggregate functions.
# File lib/sequel/adapters/shared/postgres.rb 2203 def supports_filtered_aggregates? 2204 server_version >= 90400 2205 end
PostgreSQL supports quoted function names.
# File lib/sequel/adapters/shared/postgres.rb 2208 def supports_quoted_function_names? 2209 true 2210 end
Use FROM to specify additional tables in an update query
# File lib/sequel/adapters/shared/postgres.rb 2221 def update_from_sql(sql) 2222 join_from_sql(:FROM, sql) 2223 end
Only include the primary table in the main update clause
# File lib/sequel/adapters/shared/postgres.rb 2226 def update_table_sql(sql) 2227 sql << ' ' 2228 source_list_append(sql, @opts[:from][0..0]) 2229 end