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 1355 def analyze 1356 explain(:analyze=>true) 1357 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 1362 def complex_expression_sql_append(sql, op, args) 1363 case op 1364 when :^ 1365 j = ' # ' 1366 c = false 1367 args.each do |a| 1368 sql << j if c 1369 literal_append(sql, a) 1370 c ||= true 1371 end 1372 when :ILIKE, :'NOT ILIKE' 1373 sql << '(' 1374 literal_append(sql, args[0]) 1375 sql << ' ' << op.to_s << ' ' 1376 literal_append(sql, args[1]) 1377 sql << " ESCAPE " 1378 literal_append(sql, "\\") 1379 sql << ')' 1380 else 1381 super 1382 end 1383 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 1399 def disable_insert_returning 1400 clone(:disable_insert_returning=>true) 1401 end
Return the results of an EXPLAIN query as a string
# File lib/sequel/adapters/shared/postgres.rb 1404 def explain(opts=OPTS) 1405 with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map(:'QUERY PLAN').join("\r\n") 1406 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 1432 def full_text_search(cols, terms, opts = OPTS) 1433 lang = Sequel.cast(opts[:language] || 'simple', :regconfig) 1434 1435 unless opts[:tsvector] 1436 phrase_cols = full_text_string_join(cols) 1437 cols = Sequel.function(:to_tsvector, lang, phrase_cols) 1438 end 1439 1440 unless opts[:tsquery] 1441 phrase_terms = terms.is_a?(Array) ? terms.join(' | ') : terms 1442 1443 query_func = case to_tsquery = opts[:to_tsquery] 1444 when :phrase, :plain 1445 :"#{to_tsquery}to_tsquery" 1446 else 1447 (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery 1448 end 1449 1450 terms = Sequel.function(query_func, lang, phrase_terms) 1451 end 1452 1453 ds = where(Sequel.lit(["", " @@ ", ""], cols, terms)) 1454 1455 if opts[:phrase] 1456 raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery] 1457 ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true) 1458 end 1459 1460 if opts[:rank] 1461 ds = ds.reverse{ts_rank_cd(cols, terms)} 1462 end 1463 1464 if opts[:headline] 1465 ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)} 1466 end 1467 1468 ds 1469 end
Insert given values into the database.
# File lib/sequel/adapters/shared/postgres.rb 1472 def insert(*values) 1473 if @opts[:returning] 1474 # Already know which columns to return, let the standard code handle it 1475 super 1476 elsif @opts[:sql] || @opts[:disable_insert_returning] 1477 # Raw SQL used or RETURNING disabled, just use the default behavior 1478 # and return nil since sequence is not known. 1479 super 1480 nil 1481 else 1482 # Force the use of RETURNING with the primary key value, 1483 # unless it has been disabled. 1484 returning(insert_pk).insert(*values){|r| return r.values.first} 1485 end 1486 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 1523 def insert_conflict(opts=OPTS) 1524 clone(:insert_conflict => opts) 1525 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 1533 def insert_ignore 1534 insert_conflict 1535 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 1540 def insert_select(*values) 1541 return unless supports_insert_select? 1542 # Handle case where query does not return a row 1543 server?(:default).with_sql_first(insert_select_sql(*values)) || false 1544 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 1548 def insert_select_sql(*values) 1549 ds = opts[:returning] ? self : returning 1550 ds.insert_sql(*values) 1551 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 1558 def lock(mode, opts=OPTS) 1559 if block_given? # perform locking inside a transaction and yield to block 1560 @db.transaction(opts){lock(mode, opts); yield} 1561 else 1562 sql = 'LOCK TABLE '.dup 1563 source_list_append(sql, @opts[:from]) 1564 mode = mode.to_s.upcase.strip 1565 unless LOCK_MODES.include?(mode) 1566 raise Error, "Unsupported lock mode: #{mode}" 1567 end 1568 sql << " IN #{mode} MODE" 1569 @db.execute(sql, opts) 1570 end 1571 nil 1572 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 1577 def overriding_system_value 1578 clone(:override=>:system) 1579 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 1583 def overriding_user_value 1584 clone(:override=>:user) 1585 end
# File lib/sequel/adapters/shared/postgres.rb 1587 def supports_cte?(type=:select) 1588 if type == :select 1589 server_version >= 80400 1590 else 1591 server_version >= 90100 1592 end 1593 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 1597 def supports_cte_in_subqueries? 1598 supports_cte? 1599 end
DISTINCT ON is a PostgreSQL extension
# File lib/sequel/adapters/shared/postgres.rb 1602 def supports_distinct_on? 1603 true 1604 end
PostgreSQL 9.5+ supports GROUP CUBE
# File lib/sequel/adapters/shared/postgres.rb 1607 def supports_group_cube? 1608 server_version >= 90500 1609 end
PostgreSQL 9.5+ supports GROUP ROLLUP
# File lib/sequel/adapters/shared/postgres.rb 1612 def supports_group_rollup? 1613 server_version >= 90500 1614 end
PostgreSQL 9.5+ supports GROUPING SETS
# File lib/sequel/adapters/shared/postgres.rb 1617 def supports_grouping_sets? 1618 server_version >= 90500 1619 end
PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.
# File lib/sequel/adapters/shared/postgres.rb 1627 def supports_insert_conflict? 1628 server_version >= 90500 1629 end
True unless insert returning has been disabled for this dataset.
# File lib/sequel/adapters/shared/postgres.rb 1622 def supports_insert_select? 1623 !@opts[:disable_insert_returning] 1624 end
PostgreSQL 9.3+ supports lateral subqueries
# File lib/sequel/adapters/shared/postgres.rb 1632 def supports_lateral_subqueries? 1633 server_version >= 90300 1634 end
PostgreSQL supports modifying joined datasets
# File lib/sequel/adapters/shared/postgres.rb 1637 def supports_modifying_joins? 1638 true 1639 end
PostgreSQL supports NOWAIT.
# File lib/sequel/adapters/shared/postgres.rb 1642 def supports_nowait? 1643 true 1644 end
PostgreSQL supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/postgres.rb 1652 def supports_regexp? 1653 true 1654 end
Returning is always supported.
# File lib/sequel/adapters/shared/postgres.rb 1647 def supports_returning?(type) 1648 true 1649 end
PostgreSQL 9.5+ supports SKIP LOCKED.
# File lib/sequel/adapters/shared/postgres.rb 1657 def supports_skip_locked? 1658 server_version >= 90500 1659 end
PostgreSQL supports timezones in literal timestamps
# File lib/sequel/adapters/shared/postgres.rb 1662 def supports_timestamp_timezones? 1663 true 1664 end
PostgreSQL 8.4+ supports window functions
# File lib/sequel/adapters/shared/postgres.rb 1667 def supports_window_functions? 1668 server_version >= 80400 1669 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 1687 def truncate(opts = OPTS) 1688 if opts.empty? 1689 super() 1690 else 1691 clone(:truncate_opts=>opts).truncate 1692 end 1693 end
Return a clone of the dataset with an addition named window that can be referenced in window functions. See Sequel::SQL::Window
for a list of options that can be passed in.
# File lib/sequel/adapters/shared/postgres.rb 1698 def window(name, opts) 1699 clone(:window=>(@opts[:window]||[]) + [[name, SQL::Window.new(opts)]]) 1700 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 1708 def _import(columns, values, opts=OPTS) 1709 if @opts[:returning] 1710 statements = multi_insert_sql(columns, values) 1711 @db.transaction(Hash[opts].merge!(:server=>@opts[:server])) do 1712 statements.map{|st| returning_fetch_rows(st)} 1713 end.first.map{|v| v.length == 1 ? v.values.first : v} 1714 elsif opts[:return] == :primary_key 1715 returning(insert_pk)._import(columns, values, opts) 1716 else 1717 super 1718 end 1719 end
Private Instance Methods
Format TRUNCATE statement with PostgreSQL specific options.
# File lib/sequel/adapters/shared/postgres.rb 1724 def _truncate_sql(table) 1725 to = @opts[:truncate_opts] || OPTS 1726 "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}" 1727 end
Allow truncation of multiple source tables.
# File lib/sequel/adapters/shared/postgres.rb 1730 def check_truncation_allowed! 1731 raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group] 1732 raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join] 1733 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 1856 def compound_dataset_sql_append(sql, ds) 1857 sql << '(' 1858 super 1859 sql << ')' 1860 end
Only include the primary table in the main delete clause
# File lib/sequel/adapters/shared/postgres.rb 1736 def delete_from_sql(sql) 1737 sql << ' FROM ' 1738 source_list_append(sql, @opts[:from][0..0]) 1739 end
Use USING to specify additional tables in a delete query
# File lib/sequel/adapters/shared/postgres.rb 1742 def delete_using_sql(sql) 1743 join_from_sql(:USING, sql) 1744 end
Concatenate the expressions with a space in between
# File lib/sequel/adapters/shared/postgres.rb 1934 def full_text_string_join(cols) 1935 cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')} 1936 cols = cols.zip([' '] * cols.length).flatten 1937 cols.pop 1938 SQL::StringExpression.new(:'||', *cols) 1939 end
Add ON CONFLICT clause if it should be used
# File lib/sequel/adapters/shared/postgres.rb 1747 def insert_conflict_sql(sql) 1748 if opts = @opts[:insert_conflict] 1749 sql << " ON CONFLICT" 1750 1751 if target = opts[:constraint] 1752 sql << " ON CONSTRAINT " 1753 identifier_append(sql, target) 1754 elsif target = opts[:target] 1755 sql << ' ' 1756 identifier_append(sql, Array(target)) 1757 if conflict_where = opts[:conflict_where] 1758 sql << " WHERE " 1759 literal_append(sql, conflict_where) 1760 end 1761 end 1762 1763 if values = opts[:update] 1764 sql << " DO UPDATE SET " 1765 update_sql_values_hash(sql, values) 1766 if update_where = opts[:update_where] 1767 sql << " WHERE " 1768 literal_append(sql, update_where) 1769 end 1770 else 1771 sql << " DO NOTHING" 1772 end 1773 end 1774 end
Return the primary key to use for RETURNING in an INSERT statement
# File lib/sequel/adapters/shared/postgres.rb 1777 def insert_pk 1778 if (f = opts[:from]) && !f.empty? 1779 case t = f.first 1780 when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier 1781 if pk = db.primary_key(t) 1782 Sequel::SQL::Identifier.new(pk) 1783 end 1784 end 1785 end 1786 end
Support OVERRIDING SYSTEM|USER VALUE in insert statements
# File lib/sequel/adapters/shared/postgres.rb 1789 def insert_values_sql(sql) 1790 case opts[:override] 1791 when :system 1792 sql << " OVERRIDING SYSTEM VALUE" 1793 when :user 1794 sql << " OVERRIDING USER VALUE" 1795 end 1796 super 1797 end
For multiple table support, PostgreSQL requires at least two from tables, with joins allowed.
# File lib/sequel/adapters/shared/postgres.rb 1801 def join_from_sql(type, sql) 1802 if(from = @opts[:from][1..-1]).empty? 1803 raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join] 1804 else 1805 sql << ' ' << type.to_s << ' ' 1806 source_list_append(sql, from) 1807 select_join_sql(sql) 1808 end 1809 end
Use a generic blob quoting method, hopefully overridden in one of the subadapter methods
# File lib/sequel/adapters/shared/postgres.rb 1812 def literal_blob_append(sql, v) 1813 sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'" 1814 end
PostgreSQL uses FALSE for false values
# File lib/sequel/adapters/shared/postgres.rb 1817 def literal_false 1818 'false' 1819 end
PostgreSQL quotes NaN and Infinity.
# File lib/sequel/adapters/shared/postgres.rb 1822 def literal_float(value) 1823 if value.finite? 1824 super 1825 elsif value.nan? 1826 "'NaN'" 1827 elsif value.infinite? == 1 1828 "'Infinity'" 1829 else 1830 "'-Infinity'" 1831 end 1832 end
Assume that SQL
standard quoting is on, per Sequel's defaults
# File lib/sequel/adapters/shared/postgres.rb 1835 def literal_string_append(sql, v) 1836 sql << "'" << v.gsub("'", "''") << "'" 1837 end
PostgreSQL uses true for true values
# File lib/sequel/adapters/shared/postgres.rb 1840 def literal_true 1841 'true' 1842 end
PostgreSQL supports multiple rows in INSERT.
# File lib/sequel/adapters/shared/postgres.rb 1845 def multi_insert_sql_strategy 1846 :values 1847 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 1864 def requires_like_escape? 1865 false 1866 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 1870 def select_lock_sql(sql) 1871 lock = @opts[:lock] 1872 if lock == :share 1873 sql << ' FOR SHARE' 1874 else 1875 super 1876 end 1877 1878 if lock 1879 if @opts[:skip_locked] 1880 sql << " SKIP LOCKED" 1881 elsif @opts[:nowait] 1882 sql << " NOWAIT" 1883 end 1884 end 1885 end
Support VALUES clause instead of the SELECT clause to return rows.
# File lib/sequel/adapters/shared/postgres.rb 1888 def select_values_sql(sql) 1889 sql << "VALUES " 1890 expression_list_append(sql, opts[:values]) 1891 end
SQL
fragment for named window specifications
# File lib/sequel/adapters/shared/postgres.rb 1894 def select_window_sql(sql) 1895 if ws = @opts[:window] 1896 sql << " WINDOW " 1897 c = false 1898 co = ', ' 1899 as = ' AS ' 1900 ws.map do |name, window| 1901 sql << co if c 1902 literal_append(sql, name) 1903 sql << as 1904 literal_append(sql, window) 1905 c ||= true 1906 end 1907 end 1908 end
Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive
# File lib/sequel/adapters/shared/postgres.rb 1911 def select_with_sql_base 1912 opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super 1913 end
The version of the database server
# File lib/sequel/adapters/shared/postgres.rb 1916 def server_version 1917 db.server_version(@opts[:server]) 1918 end
PostgreSQL supports quoted function names.
# File lib/sequel/adapters/shared/postgres.rb 1921 def supports_quoted_function_names? 1922 true 1923 end
# File lib/sequel/adapters/shared/postgres.rb 1925 def to_prepared_statement(type, *a) 1926 if type == :insert && !@opts.has_key?(:returning) 1927 returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a) 1928 else 1929 super 1930 end 1931 end
Use FROM to specify additional tables in an update query
# File lib/sequel/adapters/shared/postgres.rb 1942 def update_from_sql(sql) 1943 join_from_sql(:FROM, sql) 1944 end
Only include the primary table in the main update clause
# File lib/sequel/adapters/shared/postgres.rb 1947 def update_table_sql(sql) 1948 sql << ' ' 1949 source_list_append(sql, @opts[:from][0..0]) 1950 end