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 1520 def analyze 1521 explain(:analyze=>true) 1522 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 1527 def complex_expression_sql_append(sql, op, args) 1528 case op 1529 when :^ 1530 j = ' # ' 1531 c = false 1532 args.each do |a| 1533 sql << j if c 1534 literal_append(sql, a) 1535 c ||= true 1536 end 1537 when :ILIKE, :'NOT ILIKE' 1538 sql << '(' 1539 literal_append(sql, args[0]) 1540 sql << ' ' << op.to_s << ' ' 1541 literal_append(sql, args[1]) 1542 sql << " ESCAPE " 1543 literal_append(sql, "\\") 1544 sql << ')' 1545 else 1546 super 1547 end 1548 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 1564 def disable_insert_returning 1565 clone(:disable_insert_returning=>true) 1566 end
Return the results of an EXPLAIN query as a string
# File lib/sequel/adapters/shared/postgres.rb 1569 def explain(opts=OPTS) 1570 with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map(:'QUERY PLAN').join("\r\n") 1571 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 1597 def full_text_search(cols, terms, opts = OPTS) 1598 lang = Sequel.cast(opts[:language] || 'simple', :regconfig) 1599 1600 unless opts[:tsvector] 1601 phrase_cols = full_text_string_join(cols) 1602 cols = Sequel.function(:to_tsvector, lang, phrase_cols) 1603 end 1604 1605 unless opts[:tsquery] 1606 phrase_terms = terms.is_a?(Array) ? terms.join(' | ') : terms 1607 1608 query_func = case to_tsquery = opts[:to_tsquery] 1609 when :phrase, :plain 1610 :"#{to_tsquery}to_tsquery" 1611 else 1612 (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery 1613 end 1614 1615 terms = Sequel.function(query_func, lang, phrase_terms) 1616 end 1617 1618 ds = where(Sequel.lit(["", " @@ ", ""], cols, terms)) 1619 1620 if opts[:phrase] 1621 raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery] 1622 ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true) 1623 end 1624 1625 if opts[:rank] 1626 ds = ds.reverse{ts_rank_cd(cols, terms)} 1627 end 1628 1629 if opts[:headline] 1630 ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)} 1631 end 1632 1633 ds 1634 end
Insert given values into the database.
# File lib/sequel/adapters/shared/postgres.rb 1637 def insert(*values) 1638 if @opts[:returning] 1639 # Already know which columns to return, let the standard code handle it 1640 super 1641 elsif @opts[:sql] || @opts[:disable_insert_returning] 1642 # Raw SQL used or RETURNING disabled, just use the default behavior 1643 # and return nil since sequence is not known. 1644 super 1645 nil 1646 else 1647 # Force the use of RETURNING with the primary key value, 1648 # unless it has been disabled. 1649 returning(insert_pk).insert(*values){|r| return r.values.first} 1650 end 1651 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 1688 def insert_conflict(opts=OPTS) 1689 clone(:insert_conflict => opts) 1690 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 1698 def insert_ignore 1699 insert_conflict 1700 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 1705 def insert_select(*values) 1706 return unless supports_insert_select? 1707 # Handle case where query does not return a row 1708 server?(:default).with_sql_first(insert_select_sql(*values)) || false 1709 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 1713 def insert_select_sql(*values) 1714 ds = opts[:returning] ? self : returning 1715 ds.insert_sql(*values) 1716 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 1723 def lock(mode, opts=OPTS) 1724 if block_given? # perform locking inside a transaction and yield to block 1725 @db.transaction(opts){lock(mode, opts); yield} 1726 else 1727 sql = 'LOCK TABLE '.dup 1728 source_list_append(sql, @opts[:from]) 1729 mode = mode.to_s.upcase.strip 1730 unless LOCK_MODES.include?(mode) 1731 raise Error, "Unsupported lock mode: #{mode}" 1732 end 1733 sql << " IN #{mode} MODE" 1734 @db.execute(sql, opts) 1735 end 1736 nil 1737 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 1742 def overriding_system_value 1743 clone(:override=>:system) 1744 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 1748 def overriding_user_value 1749 clone(:override=>:user) 1750 end
# File lib/sequel/adapters/shared/postgres.rb 1752 def supports_cte?(type=:select) 1753 if type == :select 1754 server_version >= 80400 1755 else 1756 server_version >= 90100 1757 end 1758 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 1762 def supports_cte_in_subqueries? 1763 supports_cte? 1764 end
DISTINCT ON is a PostgreSQL extension
# File lib/sequel/adapters/shared/postgres.rb 1767 def supports_distinct_on? 1768 true 1769 end
PostgreSQL 9.5+ supports GROUP CUBE
# File lib/sequel/adapters/shared/postgres.rb 1772 def supports_group_cube? 1773 server_version >= 90500 1774 end
PostgreSQL 9.5+ supports GROUP ROLLUP
# File lib/sequel/adapters/shared/postgres.rb 1777 def supports_group_rollup? 1778 server_version >= 90500 1779 end
PostgreSQL 9.5+ supports GROUPING SETS
# File lib/sequel/adapters/shared/postgres.rb 1782 def supports_grouping_sets? 1783 server_version >= 90500 1784 end
PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.
# File lib/sequel/adapters/shared/postgres.rb 1792 def supports_insert_conflict? 1793 server_version >= 90500 1794 end
True unless insert returning has been disabled for this dataset.
# File lib/sequel/adapters/shared/postgres.rb 1787 def supports_insert_select? 1788 !@opts[:disable_insert_returning] 1789 end
PostgreSQL 9.3+ supports lateral subqueries
# File lib/sequel/adapters/shared/postgres.rb 1797 def supports_lateral_subqueries? 1798 server_version >= 90300 1799 end
PostgreSQL supports modifying joined datasets
# File lib/sequel/adapters/shared/postgres.rb 1802 def supports_modifying_joins? 1803 true 1804 end
PostgreSQL supports NOWAIT.
# File lib/sequel/adapters/shared/postgres.rb 1807 def supports_nowait? 1808 true 1809 end
PostgreSQL supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/postgres.rb 1817 def supports_regexp? 1818 true 1819 end
Returning is always supported.
# File lib/sequel/adapters/shared/postgres.rb 1812 def supports_returning?(type) 1813 true 1814 end
PostgreSQL 9.5+ supports SKIP LOCKED.
# File lib/sequel/adapters/shared/postgres.rb 1822 def supports_skip_locked? 1823 server_version >= 90500 1824 end
PostgreSQL supports timezones in literal timestamps
# File lib/sequel/adapters/shared/postgres.rb 1827 def supports_timestamp_timezones? 1828 true 1829 end
PostgreSQL 8.4+ supports WINDOW clause.
# File lib/sequel/adapters/shared/postgres.rb 1832 def supports_window_clause? 1833 server_version >= 80400 1834 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 1843 def supports_window_function_frame_option?(option) 1844 case option 1845 when :rows, :range 1846 true 1847 when :offset 1848 server_version >= 90000 1849 when :groups, :exclude 1850 server_version >= 110000 1851 end 1852 end
PostgreSQL 8.4+ supports window functions
# File lib/sequel/adapters/shared/postgres.rb 1837 def supports_window_functions? 1838 server_version >= 80400 1839 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 1870 def truncate(opts = OPTS) 1871 if opts.empty? 1872 super() 1873 else 1874 clone(:truncate_opts=>opts).truncate 1875 end 1876 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 1884 def _import(columns, values, opts=OPTS) 1885 if @opts[:returning] 1886 statements = multi_insert_sql(columns, values) 1887 trans_opts = Hash[opts] 1888 trans_opts[:server] = @opts[:server] 1889 @db.transaction(trans_opts) do 1890 statements.map{|st| returning_fetch_rows(st)} 1891 end.first.map{|v| v.length == 1 ? v.values.first : v} 1892 elsif opts[:return] == :primary_key 1893 returning(insert_pk)._import(columns, values, opts) 1894 else 1895 super 1896 end 1897 end
Private Instance Methods
Format TRUNCATE statement with PostgreSQL specific options.
# File lib/sequel/adapters/shared/postgres.rb 1902 def _truncate_sql(table) 1903 to = @opts[:truncate_opts] || OPTS 1904 "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}" 1905 end
Allow truncation of multiple source tables.
# File lib/sequel/adapters/shared/postgres.rb 1908 def check_truncation_allowed! 1909 raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group] 1910 raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join] 1911 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 2044 def compound_dataset_sql_append(sql, ds) 2045 sql << '(' 2046 super 2047 sql << ')' 2048 end
Only include the primary table in the main delete clause
# File lib/sequel/adapters/shared/postgres.rb 1914 def delete_from_sql(sql) 1915 sql << ' FROM ' 1916 source_list_append(sql, @opts[:from][0..0]) 1917 end
Use USING to specify additional tables in a delete query
# File lib/sequel/adapters/shared/postgres.rb 1920 def delete_using_sql(sql) 1921 join_from_sql(:USING, sql) 1922 end
Concatenate the expressions with a space in between
# File lib/sequel/adapters/shared/postgres.rb 2122 def full_text_string_join(cols) 2123 cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')} 2124 cols = cols.zip([' '] * cols.length).flatten 2125 cols.pop 2126 SQL::StringExpression.new(:'||', *cols) 2127 end
Add ON CONFLICT clause if it should be used
# File lib/sequel/adapters/shared/postgres.rb 1925 def insert_conflict_sql(sql) 1926 if opts = @opts[:insert_conflict] 1927 sql << " ON CONFLICT" 1928 1929 if target = opts[:constraint] 1930 sql << " ON CONSTRAINT " 1931 identifier_append(sql, target) 1932 elsif target = opts[:target] 1933 sql << ' ' 1934 identifier_append(sql, Array(target)) 1935 if conflict_where = opts[:conflict_where] 1936 sql << " WHERE " 1937 literal_append(sql, conflict_where) 1938 end 1939 end 1940 1941 if values = opts[:update] 1942 sql << " DO UPDATE SET " 1943 update_sql_values_hash(sql, values) 1944 if update_where = opts[:update_where] 1945 sql << " WHERE " 1946 literal_append(sql, update_where) 1947 end 1948 else 1949 sql << " DO NOTHING" 1950 end 1951 end 1952 end
Include aliases when inserting into a single table on PostgreSQL 9.5+.
# File lib/sequel/adapters/shared/postgres.rb 1955 def insert_into_sql(sql) 1956 sql << " INTO " 1957 if (f = @opts[:from]) && f.length == 1 1958 identifier_append(sql, server_version >= 90500 ? f.first : unaliased_identifier(f.first)) 1959 else 1960 source_list_append(sql, f) 1961 end 1962 end
Return the primary key to use for RETURNING in an INSERT statement
# File lib/sequel/adapters/shared/postgres.rb 1965 def insert_pk 1966 if (f = opts[:from]) && !f.empty? 1967 case t = f.first 1968 when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier 1969 if pk = db.primary_key(t) 1970 Sequel::SQL::Identifier.new(pk) 1971 end 1972 end 1973 end 1974 end
Support OVERRIDING SYSTEM|USER VALUE in insert statements
# File lib/sequel/adapters/shared/postgres.rb 1977 def insert_values_sql(sql) 1978 case opts[:override] 1979 when :system 1980 sql << " OVERRIDING SYSTEM VALUE" 1981 when :user 1982 sql << " OVERRIDING USER VALUE" 1983 end 1984 super 1985 end
For multiple table support, PostgreSQL requires at least two from tables, with joins allowed.
# File lib/sequel/adapters/shared/postgres.rb 1989 def join_from_sql(type, sql) 1990 if(from = @opts[:from][1..-1]).empty? 1991 raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join] 1992 else 1993 sql << ' ' << type.to_s << ' ' 1994 source_list_append(sql, from) 1995 select_join_sql(sql) 1996 end 1997 end
Use a generic blob quoting method, hopefully overridden in one of the subadapter methods
# File lib/sequel/adapters/shared/postgres.rb 2000 def literal_blob_append(sql, v) 2001 sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'" 2002 end
PostgreSQL uses FALSE for false values
# File lib/sequel/adapters/shared/postgres.rb 2005 def literal_false 2006 'false' 2007 end
PostgreSQL quotes NaN and Infinity.
# File lib/sequel/adapters/shared/postgres.rb 2010 def literal_float(value) 2011 if value.finite? 2012 super 2013 elsif value.nan? 2014 "'NaN'" 2015 elsif value.infinite? == 1 2016 "'Infinity'" 2017 else 2018 "'-Infinity'" 2019 end 2020 end
Assume that SQL
standard quoting is on, per Sequel's defaults
# File lib/sequel/adapters/shared/postgres.rb 2023 def literal_string_append(sql, v) 2024 sql << "'" << v.gsub("'", "''") << "'" 2025 end
PostgreSQL uses true for true values
# File lib/sequel/adapters/shared/postgres.rb 2028 def literal_true 2029 'true' 2030 end
PostgreSQL supports multiple rows in INSERT.
# File lib/sequel/adapters/shared/postgres.rb 2033 def multi_insert_sql_strategy 2034 :values 2035 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 2052 def requires_like_escape? 2053 false 2054 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 2058 def select_lock_sql(sql) 2059 lock = @opts[:lock] 2060 if lock == :share 2061 sql << ' FOR SHARE' 2062 else 2063 super 2064 end 2065 2066 if lock 2067 if @opts[:skip_locked] 2068 sql << " SKIP LOCKED" 2069 elsif @opts[:nowait] 2070 sql << " NOWAIT" 2071 end 2072 end 2073 end
Support VALUES clause instead of the SELECT clause to return rows.
# File lib/sequel/adapters/shared/postgres.rb 2076 def select_values_sql(sql) 2077 sql << "VALUES " 2078 expression_list_append(sql, opts[:values]) 2079 end
Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive
# File lib/sequel/adapters/shared/postgres.rb 2082 def select_with_sql_base 2083 opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super 2084 end
Support WITH AS [NOT] MATERIALIZED if :materialized option is used.
# File lib/sequel/adapters/shared/postgres.rb 2087 def select_with_sql_prefix(sql, w) 2088 super 2089 2090 case w[:materialized] 2091 when true 2092 sql << "MATERIALIZED " 2093 when false 2094 sql << "NOT MATERIALIZED " 2095 end 2096 end
The version of the database server
# File lib/sequel/adapters/shared/postgres.rb 2099 def server_version 2100 db.server_version(@opts[:server]) 2101 end
PostgreSQL 9.4+ supports the FILTER clause for aggregate functions.
# File lib/sequel/adapters/shared/postgres.rb 2104 def supports_filtered_aggregates? 2105 server_version >= 90400 2106 end
PostgreSQL supports quoted function names.
# File lib/sequel/adapters/shared/postgres.rb 2109 def supports_quoted_function_names? 2110 true 2111 end
# File lib/sequel/adapters/shared/postgres.rb 2113 def to_prepared_statement(type, *a) 2114 if type == :insert && !@opts.has_key?(:returning) 2115 returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a) 2116 else 2117 super 2118 end 2119 end
Use FROM to specify additional tables in an update query
# File lib/sequel/adapters/shared/postgres.rb 2130 def update_from_sql(sql) 2131 join_from_sql(:FROM, sql) 2132 end
Only include the primary table in the main update clause
# File lib/sequel/adapters/shared/postgres.rb 2135 def update_table_sql(sql) 2136 sql << ' ' 2137 source_list_append(sql, @opts[:from][0..0]) 2138 end