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 1375 def analyze 1376 explain(:analyze=>true) 1377 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 1382 def complex_expression_sql_append(sql, op, args) 1383 case op 1384 when :^ 1385 j = ' # ' 1386 c = false 1387 args.each do |a| 1388 sql << j if c 1389 literal_append(sql, a) 1390 c ||= true 1391 end 1392 when :ILIKE, :'NOT ILIKE' 1393 sql << '(' 1394 literal_append(sql, args[0]) 1395 sql << ' ' << op.to_s << ' ' 1396 literal_append(sql, args[1]) 1397 sql << " ESCAPE " 1398 literal_append(sql, "\\") 1399 sql << ')' 1400 else 1401 super 1402 end 1403 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 1419 def disable_insert_returning 1420 clone(:disable_insert_returning=>true) 1421 end
Return the results of an EXPLAIN query as a string
# File lib/sequel/adapters/shared/postgres.rb 1424 def explain(opts=OPTS) 1425 with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map(:'QUERY PLAN').join("\r\n") 1426 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 1452 def full_text_search(cols, terms, opts = OPTS) 1453 lang = Sequel.cast(opts[:language] || 'simple', :regconfig) 1454 1455 unless opts[:tsvector] 1456 phrase_cols = full_text_string_join(cols) 1457 cols = Sequel.function(:to_tsvector, lang, phrase_cols) 1458 end 1459 1460 unless opts[:tsquery] 1461 phrase_terms = terms.is_a?(Array) ? terms.join(' | ') : terms 1462 1463 query_func = case to_tsquery = opts[:to_tsquery] 1464 when :phrase, :plain 1465 :"#{to_tsquery}to_tsquery" 1466 else 1467 (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery 1468 end 1469 1470 terms = Sequel.function(query_func, lang, phrase_terms) 1471 end 1472 1473 ds = where(Sequel.lit(["", " @@ ", ""], cols, terms)) 1474 1475 if opts[:phrase] 1476 raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery] 1477 ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true) 1478 end 1479 1480 if opts[:rank] 1481 ds = ds.reverse{ts_rank_cd(cols, terms)} 1482 end 1483 1484 if opts[:headline] 1485 ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)} 1486 end 1487 1488 ds 1489 end
Insert given values into the database.
# File lib/sequel/adapters/shared/postgres.rb 1492 def insert(*values) 1493 if @opts[:returning] 1494 # Already know which columns to return, let the standard code handle it 1495 super 1496 elsif @opts[:sql] || @opts[:disable_insert_returning] 1497 # Raw SQL used or RETURNING disabled, just use the default behavior 1498 # and return nil since sequence is not known. 1499 super 1500 nil 1501 else 1502 # Force the use of RETURNING with the primary key value, 1503 # unless it has been disabled. 1504 returning(insert_pk).insert(*values){|r| return r.values.first} 1505 end 1506 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 1543 def insert_conflict(opts=OPTS) 1544 clone(:insert_conflict => opts) 1545 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 1553 def insert_ignore 1554 insert_conflict 1555 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 1560 def insert_select(*values) 1561 return unless supports_insert_select? 1562 # Handle case where query does not return a row 1563 server?(:default).with_sql_first(insert_select_sql(*values)) || false 1564 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 1568 def insert_select_sql(*values) 1569 ds = opts[:returning] ? self : returning 1570 ds.insert_sql(*values) 1571 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 1578 def lock(mode, opts=OPTS) 1579 if block_given? # perform locking inside a transaction and yield to block 1580 @db.transaction(opts){lock(mode, opts); yield} 1581 else 1582 sql = 'LOCK TABLE '.dup 1583 source_list_append(sql, @opts[:from]) 1584 mode = mode.to_s.upcase.strip 1585 unless LOCK_MODES.include?(mode) 1586 raise Error, "Unsupported lock mode: #{mode}" 1587 end 1588 sql << " IN #{mode} MODE" 1589 @db.execute(sql, opts) 1590 end 1591 nil 1592 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 1597 def overriding_system_value 1598 clone(:override=>:system) 1599 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 1603 def overriding_user_value 1604 clone(:override=>:user) 1605 end
# File lib/sequel/adapters/shared/postgres.rb 1607 def supports_cte?(type=:select) 1608 if type == :select 1609 server_version >= 80400 1610 else 1611 server_version >= 90100 1612 end 1613 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 1617 def supports_cte_in_subqueries? 1618 supports_cte? 1619 end
DISTINCT ON is a PostgreSQL extension
# File lib/sequel/adapters/shared/postgres.rb 1622 def supports_distinct_on? 1623 true 1624 end
PostgreSQL 9.5+ supports GROUP CUBE
# File lib/sequel/adapters/shared/postgres.rb 1627 def supports_group_cube? 1628 server_version >= 90500 1629 end
PostgreSQL 9.5+ supports GROUP ROLLUP
# File lib/sequel/adapters/shared/postgres.rb 1632 def supports_group_rollup? 1633 server_version >= 90500 1634 end
PostgreSQL 9.5+ supports GROUPING SETS
# File lib/sequel/adapters/shared/postgres.rb 1637 def supports_grouping_sets? 1638 server_version >= 90500 1639 end
PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.
# File lib/sequel/adapters/shared/postgres.rb 1647 def supports_insert_conflict? 1648 server_version >= 90500 1649 end
True unless insert returning has been disabled for this dataset.
# File lib/sequel/adapters/shared/postgres.rb 1642 def supports_insert_select? 1643 !@opts[:disable_insert_returning] 1644 end
PostgreSQL 9.3+ supports lateral subqueries
# File lib/sequel/adapters/shared/postgres.rb 1652 def supports_lateral_subqueries? 1653 server_version >= 90300 1654 end
PostgreSQL supports modifying joined datasets
# File lib/sequel/adapters/shared/postgres.rb 1657 def supports_modifying_joins? 1658 true 1659 end
PostgreSQL supports NOWAIT.
# File lib/sequel/adapters/shared/postgres.rb 1662 def supports_nowait? 1663 true 1664 end
PostgreSQL supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/postgres.rb 1672 def supports_regexp? 1673 true 1674 end
Returning is always supported.
# File lib/sequel/adapters/shared/postgres.rb 1667 def supports_returning?(type) 1668 true 1669 end
PostgreSQL 9.5+ supports SKIP LOCKED.
# File lib/sequel/adapters/shared/postgres.rb 1677 def supports_skip_locked? 1678 server_version >= 90500 1679 end
PostgreSQL supports timezones in literal timestamps
# File lib/sequel/adapters/shared/postgres.rb 1682 def supports_timestamp_timezones? 1683 true 1684 end
PostgreSQL 8.4+ supports WINDOW clause.
# File lib/sequel/adapters/shared/postgres.rb 1687 def supports_window_clause? 1688 server_version >= 80400 1689 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 1698 def supports_window_function_frame_option?(option) 1699 case option 1700 when :rows, :range 1701 true 1702 when :offset 1703 server_version >= 90000 1704 when :groups, :exclude 1705 server_version >= 110000 1706 end 1707 end
PostgreSQL 8.4+ supports window functions
# File lib/sequel/adapters/shared/postgres.rb 1692 def supports_window_functions? 1693 server_version >= 80400 1694 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 1725 def truncate(opts = OPTS) 1726 if opts.empty? 1727 super() 1728 else 1729 clone(:truncate_opts=>opts).truncate 1730 end 1731 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 1739 def _import(columns, values, opts=OPTS) 1740 if @opts[:returning] 1741 statements = multi_insert_sql(columns, values) 1742 trans_opts = Hash[opts] 1743 trans_opts[:server] = @opts[:server] 1744 @db.transaction(trans_opts) do 1745 statements.map{|st| returning_fetch_rows(st)} 1746 end.first.map{|v| v.length == 1 ? v.values.first : v} 1747 elsif opts[:return] == :primary_key 1748 returning(insert_pk)._import(columns, values, opts) 1749 else 1750 super 1751 end 1752 end
Private Instance Methods
Format TRUNCATE statement with PostgreSQL specific options.
# File lib/sequel/adapters/shared/postgres.rb 1757 def _truncate_sql(table) 1758 to = @opts[:truncate_opts] || OPTS 1759 "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}" 1760 end
Allow truncation of multiple source tables.
# File lib/sequel/adapters/shared/postgres.rb 1763 def check_truncation_allowed! 1764 raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group] 1765 raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join] 1766 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 1899 def compound_dataset_sql_append(sql, ds) 1900 sql << '(' 1901 super 1902 sql << ')' 1903 end
Only include the primary table in the main delete clause
# File lib/sequel/adapters/shared/postgres.rb 1769 def delete_from_sql(sql) 1770 sql << ' FROM ' 1771 source_list_append(sql, @opts[:from][0..0]) 1772 end
Use USING to specify additional tables in a delete query
# File lib/sequel/adapters/shared/postgres.rb 1775 def delete_using_sql(sql) 1776 join_from_sql(:USING, sql) 1777 end
Concatenate the expressions with a space in between
# File lib/sequel/adapters/shared/postgres.rb 1972 def full_text_string_join(cols) 1973 cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')} 1974 cols = cols.zip([' '] * cols.length).flatten 1975 cols.pop 1976 SQL::StringExpression.new(:'||', *cols) 1977 end
Add ON CONFLICT clause if it should be used
# File lib/sequel/adapters/shared/postgres.rb 1780 def insert_conflict_sql(sql) 1781 if opts = @opts[:insert_conflict] 1782 sql << " ON CONFLICT" 1783 1784 if target = opts[:constraint] 1785 sql << " ON CONSTRAINT " 1786 identifier_append(sql, target) 1787 elsif target = opts[:target] 1788 sql << ' ' 1789 identifier_append(sql, Array(target)) 1790 if conflict_where = opts[:conflict_where] 1791 sql << " WHERE " 1792 literal_append(sql, conflict_where) 1793 end 1794 end 1795 1796 if values = opts[:update] 1797 sql << " DO UPDATE SET " 1798 update_sql_values_hash(sql, values) 1799 if update_where = opts[:update_where] 1800 sql << " WHERE " 1801 literal_append(sql, update_where) 1802 end 1803 else 1804 sql << " DO NOTHING" 1805 end 1806 end 1807 end
Include aliases when inserting into a single table on PostgreSQL 9.5+.
# File lib/sequel/adapters/shared/postgres.rb 1810 def insert_into_sql(sql) 1811 sql << " INTO " 1812 if (f = @opts[:from]) && f.length == 1 1813 identifier_append(sql, server_version >= 90500 ? f.first : unaliased_identifier(f.first)) 1814 else 1815 source_list_append(sql, f) 1816 end 1817 end
Return the primary key to use for RETURNING in an INSERT statement
# File lib/sequel/adapters/shared/postgres.rb 1820 def insert_pk 1821 if (f = opts[:from]) && !f.empty? 1822 case t = f.first 1823 when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier 1824 if pk = db.primary_key(t) 1825 Sequel::SQL::Identifier.new(pk) 1826 end 1827 end 1828 end 1829 end
Support OVERRIDING SYSTEM|USER VALUE in insert statements
# File lib/sequel/adapters/shared/postgres.rb 1832 def insert_values_sql(sql) 1833 case opts[:override] 1834 when :system 1835 sql << " OVERRIDING SYSTEM VALUE" 1836 when :user 1837 sql << " OVERRIDING USER VALUE" 1838 end 1839 super 1840 end
For multiple table support, PostgreSQL requires at least two from tables, with joins allowed.
# File lib/sequel/adapters/shared/postgres.rb 1844 def join_from_sql(type, sql) 1845 if(from = @opts[:from][1..-1]).empty? 1846 raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join] 1847 else 1848 sql << ' ' << type.to_s << ' ' 1849 source_list_append(sql, from) 1850 select_join_sql(sql) 1851 end 1852 end
Use a generic blob quoting method, hopefully overridden in one of the subadapter methods
# File lib/sequel/adapters/shared/postgres.rb 1855 def literal_blob_append(sql, v) 1856 sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'" 1857 end
PostgreSQL uses FALSE for false values
# File lib/sequel/adapters/shared/postgres.rb 1860 def literal_false 1861 'false' 1862 end
PostgreSQL quotes NaN and Infinity.
# File lib/sequel/adapters/shared/postgres.rb 1865 def literal_float(value) 1866 if value.finite? 1867 super 1868 elsif value.nan? 1869 "'NaN'" 1870 elsif value.infinite? == 1 1871 "'Infinity'" 1872 else 1873 "'-Infinity'" 1874 end 1875 end
Assume that SQL
standard quoting is on, per Sequel's defaults
# File lib/sequel/adapters/shared/postgres.rb 1878 def literal_string_append(sql, v) 1879 sql << "'" << v.gsub("'", "''") << "'" 1880 end
PostgreSQL uses true for true values
# File lib/sequel/adapters/shared/postgres.rb 1883 def literal_true 1884 'true' 1885 end
PostgreSQL supports multiple rows in INSERT.
# File lib/sequel/adapters/shared/postgres.rb 1888 def multi_insert_sql_strategy 1889 :values 1890 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 1907 def requires_like_escape? 1908 false 1909 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 1913 def select_lock_sql(sql) 1914 lock = @opts[:lock] 1915 if lock == :share 1916 sql << ' FOR SHARE' 1917 else 1918 super 1919 end 1920 1921 if lock 1922 if @opts[:skip_locked] 1923 sql << " SKIP LOCKED" 1924 elsif @opts[:nowait] 1925 sql << " NOWAIT" 1926 end 1927 end 1928 end
Support VALUES clause instead of the SELECT clause to return rows.
# File lib/sequel/adapters/shared/postgres.rb 1931 def select_values_sql(sql) 1932 sql << "VALUES " 1933 expression_list_append(sql, opts[:values]) 1934 end
Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive
# File lib/sequel/adapters/shared/postgres.rb 1937 def select_with_sql_base 1938 opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super 1939 end
Support WITH AS [NOT] MATERIALIZED if :materialized option is used.
# File lib/sequel/adapters/shared/postgres.rb 1942 def select_with_sql_prefix(sql, w) 1943 super 1944 1945 case w[:materialized] 1946 when true 1947 sql << "MATERIALIZED " 1948 when false 1949 sql << "NOT MATERIALIZED " 1950 end 1951 end
The version of the database server
# File lib/sequel/adapters/shared/postgres.rb 1954 def server_version 1955 db.server_version(@opts[:server]) 1956 end
PostgreSQL supports quoted function names.
# File lib/sequel/adapters/shared/postgres.rb 1959 def supports_quoted_function_names? 1960 true 1961 end
# File lib/sequel/adapters/shared/postgres.rb 1963 def to_prepared_statement(type, *a) 1964 if type == :insert && !@opts.has_key?(:returning) 1965 returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a) 1966 else 1967 super 1968 end 1969 end
Use FROM to specify additional tables in an update query
# File lib/sequel/adapters/shared/postgres.rb 1980 def update_from_sql(sql) 1981 join_from_sql(:FROM, sql) 1982 end
Only include the primary table in the main update clause
# File lib/sequel/adapters/shared/postgres.rb 1985 def update_table_sql(sql) 1986 sql << ' ' 1987 source_list_append(sql, @opts[:from][0..0]) 1988 end