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 1367 def analyze 1368 explain(:analyze=>true) 1369 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 1374 def complex_expression_sql_append(sql, op, args) 1375 case op 1376 when :^ 1377 j = ' # ' 1378 c = false 1379 args.each do |a| 1380 sql << j if c 1381 literal_append(sql, a) 1382 c ||= true 1383 end 1384 when :ILIKE, :'NOT ILIKE' 1385 sql << '(' 1386 literal_append(sql, args[0]) 1387 sql << ' ' << op.to_s << ' ' 1388 literal_append(sql, args[1]) 1389 sql << " ESCAPE " 1390 literal_append(sql, "\\") 1391 sql << ')' 1392 else 1393 super 1394 end 1395 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 1411 def disable_insert_returning 1412 clone(:disable_insert_returning=>true) 1413 end
Return the results of an EXPLAIN query as a string
# File lib/sequel/adapters/shared/postgres.rb 1416 def explain(opts=OPTS) 1417 with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map(:'QUERY PLAN').join("\r\n") 1418 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 1444 def full_text_search(cols, terms, opts = OPTS) 1445 lang = Sequel.cast(opts[:language] || 'simple', :regconfig) 1446 1447 unless opts[:tsvector] 1448 phrase_cols = full_text_string_join(cols) 1449 cols = Sequel.function(:to_tsvector, lang, phrase_cols) 1450 end 1451 1452 unless opts[:tsquery] 1453 phrase_terms = terms.is_a?(Array) ? terms.join(' | ') : terms 1454 1455 query_func = case to_tsquery = opts[:to_tsquery] 1456 when :phrase, :plain 1457 :"#{to_tsquery}to_tsquery" 1458 else 1459 (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery 1460 end 1461 1462 terms = Sequel.function(query_func, lang, phrase_terms) 1463 end 1464 1465 ds = where(Sequel.lit(["", " @@ ", ""], cols, terms)) 1466 1467 if opts[:phrase] 1468 raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery] 1469 ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true) 1470 end 1471 1472 if opts[:rank] 1473 ds = ds.reverse{ts_rank_cd(cols, terms)} 1474 end 1475 1476 if opts[:headline] 1477 ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)} 1478 end 1479 1480 ds 1481 end
Insert given values into the database.
# File lib/sequel/adapters/shared/postgres.rb 1484 def insert(*values) 1485 if @opts[:returning] 1486 # Already know which columns to return, let the standard code handle it 1487 super 1488 elsif @opts[:sql] || @opts[:disable_insert_returning] 1489 # Raw SQL used or RETURNING disabled, just use the default behavior 1490 # and return nil since sequence is not known. 1491 super 1492 nil 1493 else 1494 # Force the use of RETURNING with the primary key value, 1495 # unless it has been disabled. 1496 returning(insert_pk).insert(*values){|r| return r.values.first} 1497 end 1498 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 1535 def insert_conflict(opts=OPTS) 1536 clone(:insert_conflict => opts) 1537 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 1545 def insert_ignore 1546 insert_conflict 1547 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 1552 def insert_select(*values) 1553 return unless supports_insert_select? 1554 # Handle case where query does not return a row 1555 server?(:default).with_sql_first(insert_select_sql(*values)) || false 1556 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 1560 def insert_select_sql(*values) 1561 ds = opts[:returning] ? self : returning 1562 ds.insert_sql(*values) 1563 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 1570 def lock(mode, opts=OPTS) 1571 if block_given? # perform locking inside a transaction and yield to block 1572 @db.transaction(opts){lock(mode, opts); yield} 1573 else 1574 sql = 'LOCK TABLE '.dup 1575 source_list_append(sql, @opts[:from]) 1576 mode = mode.to_s.upcase.strip 1577 unless LOCK_MODES.include?(mode) 1578 raise Error, "Unsupported lock mode: #{mode}" 1579 end 1580 sql << " IN #{mode} MODE" 1581 @db.execute(sql, opts) 1582 end 1583 nil 1584 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 1589 def overriding_system_value 1590 clone(:override=>:system) 1591 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 1595 def overriding_user_value 1596 clone(:override=>:user) 1597 end
# File lib/sequel/adapters/shared/postgres.rb 1599 def supports_cte?(type=:select) 1600 if type == :select 1601 server_version >= 80400 1602 else 1603 server_version >= 90100 1604 end 1605 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 1609 def supports_cte_in_subqueries? 1610 supports_cte? 1611 end
DISTINCT ON is a PostgreSQL extension
# File lib/sequel/adapters/shared/postgres.rb 1614 def supports_distinct_on? 1615 true 1616 end
PostgreSQL 9.5+ supports GROUP CUBE
# File lib/sequel/adapters/shared/postgres.rb 1619 def supports_group_cube? 1620 server_version >= 90500 1621 end
PostgreSQL 9.5+ supports GROUP ROLLUP
# File lib/sequel/adapters/shared/postgres.rb 1624 def supports_group_rollup? 1625 server_version >= 90500 1626 end
PostgreSQL 9.5+ supports GROUPING SETS
# File lib/sequel/adapters/shared/postgres.rb 1629 def supports_grouping_sets? 1630 server_version >= 90500 1631 end
PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.
# File lib/sequel/adapters/shared/postgres.rb 1639 def supports_insert_conflict? 1640 server_version >= 90500 1641 end
True unless insert returning has been disabled for this dataset.
# File lib/sequel/adapters/shared/postgres.rb 1634 def supports_insert_select? 1635 !@opts[:disable_insert_returning] 1636 end
PostgreSQL 9.3+ supports lateral subqueries
# File lib/sequel/adapters/shared/postgres.rb 1644 def supports_lateral_subqueries? 1645 server_version >= 90300 1646 end
PostgreSQL supports modifying joined datasets
# File lib/sequel/adapters/shared/postgres.rb 1649 def supports_modifying_joins? 1650 true 1651 end
PostgreSQL supports NOWAIT.
# File lib/sequel/adapters/shared/postgres.rb 1654 def supports_nowait? 1655 true 1656 end
PostgreSQL supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/postgres.rb 1664 def supports_regexp? 1665 true 1666 end
Returning is always supported.
# File lib/sequel/adapters/shared/postgres.rb 1659 def supports_returning?(type) 1660 true 1661 end
PostgreSQL 9.5+ supports SKIP LOCKED.
# File lib/sequel/adapters/shared/postgres.rb 1669 def supports_skip_locked? 1670 server_version >= 90500 1671 end
PostgreSQL supports timezones in literal timestamps
# File lib/sequel/adapters/shared/postgres.rb 1674 def supports_timestamp_timezones? 1675 true 1676 end
PostgreSQL 8.4+ supports WINDOW clause.
# File lib/sequel/adapters/shared/postgres.rb 1679 def supports_window_clause? 1680 server_version >= 80400 1681 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 1690 def supports_window_function_frame_option?(option) 1691 case option 1692 when :rows, :range 1693 true 1694 when :offset 1695 server_version >= 90000 1696 when :groups, :exclude 1697 server_version >= 110000 1698 end 1699 end
PostgreSQL 8.4+ supports window functions
# File lib/sequel/adapters/shared/postgres.rb 1684 def supports_window_functions? 1685 server_version >= 80400 1686 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 1717 def truncate(opts = OPTS) 1718 if opts.empty? 1719 super() 1720 else 1721 clone(:truncate_opts=>opts).truncate 1722 end 1723 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 1731 def _import(columns, values, opts=OPTS) 1732 if @opts[:returning] 1733 statements = multi_insert_sql(columns, values) 1734 trans_opts = Hash[opts] 1735 trans_opts[:server] = @opts[:server] 1736 @db.transaction(trans_opts) do 1737 statements.map{|st| returning_fetch_rows(st)} 1738 end.first.map{|v| v.length == 1 ? v.values.first : v} 1739 elsif opts[:return] == :primary_key 1740 returning(insert_pk)._import(columns, values, opts) 1741 else 1742 super 1743 end 1744 end
Private Instance Methods
Format TRUNCATE statement with PostgreSQL specific options.
# File lib/sequel/adapters/shared/postgres.rb 1749 def _truncate_sql(table) 1750 to = @opts[:truncate_opts] || OPTS 1751 "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}" 1752 end
Allow truncation of multiple source tables.
# File lib/sequel/adapters/shared/postgres.rb 1755 def check_truncation_allowed! 1756 raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group] 1757 raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join] 1758 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 1881 def compound_dataset_sql_append(sql, ds) 1882 sql << '(' 1883 super 1884 sql << ')' 1885 end
Only include the primary table in the main delete clause
# File lib/sequel/adapters/shared/postgres.rb 1761 def delete_from_sql(sql) 1762 sql << ' FROM ' 1763 source_list_append(sql, @opts[:from][0..0]) 1764 end
Use USING to specify additional tables in a delete query
# File lib/sequel/adapters/shared/postgres.rb 1767 def delete_using_sql(sql) 1768 join_from_sql(:USING, sql) 1769 end
Concatenate the expressions with a space in between
# File lib/sequel/adapters/shared/postgres.rb 1954 def full_text_string_join(cols) 1955 cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')} 1956 cols = cols.zip([' '] * cols.length).flatten 1957 cols.pop 1958 SQL::StringExpression.new(:'||', *cols) 1959 end
Add ON CONFLICT clause if it should be used
# File lib/sequel/adapters/shared/postgres.rb 1772 def insert_conflict_sql(sql) 1773 if opts = @opts[:insert_conflict] 1774 sql << " ON CONFLICT" 1775 1776 if target = opts[:constraint] 1777 sql << " ON CONSTRAINT " 1778 identifier_append(sql, target) 1779 elsif target = opts[:target] 1780 sql << ' ' 1781 identifier_append(sql, Array(target)) 1782 if conflict_where = opts[:conflict_where] 1783 sql << " WHERE " 1784 literal_append(sql, conflict_where) 1785 end 1786 end 1787 1788 if values = opts[:update] 1789 sql << " DO UPDATE SET " 1790 update_sql_values_hash(sql, values) 1791 if update_where = opts[:update_where] 1792 sql << " WHERE " 1793 literal_append(sql, update_where) 1794 end 1795 else 1796 sql << " DO NOTHING" 1797 end 1798 end 1799 end
Return the primary key to use for RETURNING in an INSERT statement
# File lib/sequel/adapters/shared/postgres.rb 1802 def insert_pk 1803 if (f = opts[:from]) && !f.empty? 1804 case t = f.first 1805 when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier 1806 if pk = db.primary_key(t) 1807 Sequel::SQL::Identifier.new(pk) 1808 end 1809 end 1810 end 1811 end
Support OVERRIDING SYSTEM|USER VALUE in insert statements
# File lib/sequel/adapters/shared/postgres.rb 1814 def insert_values_sql(sql) 1815 case opts[:override] 1816 when :system 1817 sql << " OVERRIDING SYSTEM VALUE" 1818 when :user 1819 sql << " OVERRIDING USER VALUE" 1820 end 1821 super 1822 end
For multiple table support, PostgreSQL requires at least two from tables, with joins allowed.
# File lib/sequel/adapters/shared/postgres.rb 1826 def join_from_sql(type, sql) 1827 if(from = @opts[:from][1..-1]).empty? 1828 raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join] 1829 else 1830 sql << ' ' << type.to_s << ' ' 1831 source_list_append(sql, from) 1832 select_join_sql(sql) 1833 end 1834 end
Use a generic blob quoting method, hopefully overridden in one of the subadapter methods
# File lib/sequel/adapters/shared/postgres.rb 1837 def literal_blob_append(sql, v) 1838 sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'" 1839 end
PostgreSQL uses FALSE for false values
# File lib/sequel/adapters/shared/postgres.rb 1842 def literal_false 1843 'false' 1844 end
PostgreSQL quotes NaN and Infinity.
# File lib/sequel/adapters/shared/postgres.rb 1847 def literal_float(value) 1848 if value.finite? 1849 super 1850 elsif value.nan? 1851 "'NaN'" 1852 elsif value.infinite? == 1 1853 "'Infinity'" 1854 else 1855 "'-Infinity'" 1856 end 1857 end
Assume that SQL
standard quoting is on, per Sequel's defaults
# File lib/sequel/adapters/shared/postgres.rb 1860 def literal_string_append(sql, v) 1861 sql << "'" << v.gsub("'", "''") << "'" 1862 end
PostgreSQL uses true for true values
# File lib/sequel/adapters/shared/postgres.rb 1865 def literal_true 1866 'true' 1867 end
PostgreSQL supports multiple rows in INSERT.
# File lib/sequel/adapters/shared/postgres.rb 1870 def multi_insert_sql_strategy 1871 :values 1872 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 1889 def requires_like_escape? 1890 false 1891 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 1895 def select_lock_sql(sql) 1896 lock = @opts[:lock] 1897 if lock == :share 1898 sql << ' FOR SHARE' 1899 else 1900 super 1901 end 1902 1903 if lock 1904 if @opts[:skip_locked] 1905 sql << " SKIP LOCKED" 1906 elsif @opts[:nowait] 1907 sql << " NOWAIT" 1908 end 1909 end 1910 end
Support VALUES clause instead of the SELECT clause to return rows.
# File lib/sequel/adapters/shared/postgres.rb 1913 def select_values_sql(sql) 1914 sql << "VALUES " 1915 expression_list_append(sql, opts[:values]) 1916 end
Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive
# File lib/sequel/adapters/shared/postgres.rb 1919 def select_with_sql_base 1920 opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super 1921 end
Support WITH AS [NOT] MATERIALIZED if :materialized option is used.
# File lib/sequel/adapters/shared/postgres.rb 1924 def select_with_sql_prefix(sql, w) 1925 super 1926 1927 case w[:materialized] 1928 when true 1929 sql << "MATERIALIZED " 1930 when false 1931 sql << "NOT MATERIALIZED " 1932 end 1933 end
The version of the database server
# File lib/sequel/adapters/shared/postgres.rb 1936 def server_version 1937 db.server_version(@opts[:server]) 1938 end
PostgreSQL supports quoted function names.
# File lib/sequel/adapters/shared/postgres.rb 1941 def supports_quoted_function_names? 1942 true 1943 end
# File lib/sequel/adapters/shared/postgres.rb 1945 def to_prepared_statement(type, *a) 1946 if type == :insert && !@opts.has_key?(:returning) 1947 returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a) 1948 else 1949 super 1950 end 1951 end
Use FROM to specify additional tables in an update query
# File lib/sequel/adapters/shared/postgres.rb 1962 def update_from_sql(sql) 1963 join_from_sql(:FROM, sql) 1964 end
Only include the primary table in the main update clause
# File lib/sequel/adapters/shared/postgres.rb 1967 def update_table_sql(sql) 1968 sql << ' ' 1969 source_list_append(sql, @opts[:from][0..0]) 1970 end