Module: Sequel::Postgres::DatasetMethods
- Includes:
- UnmodifiedIdentifiers::DatasetMethods
- Included in:
- JDBC::Postgres::Dataset, Dataset
- Defined in:
- lib/sequel/adapters/shared/postgres.rb
Constant Summary collapse
- NULL =
LiteralString.new('NULL').freeze
- LOCK_MODES =
['ACCESS SHARE', 'ROW SHARE', 'ROW EXCLUSIVE', 'SHARE UPDATE EXCLUSIVE', 'SHARE', 'SHARE ROW EXCLUSIVE', 'EXCLUSIVE', 'ACCESS EXCLUSIVE'].each(&:freeze).freeze
Instance Method Summary collapse
-
#analyze ⇒ Object
Return the results of an EXPLAIN ANALYZE query as a string.
-
#complex_expression_sql_append(sql, op, args) ⇒ Object
Handle converting the ruby xor operator (^) into the PostgreSQL xor operator (#), and use the ILIKE and NOT ILIKE operators.
-
#disable_insert_returning ⇒ Object
Disables automatic use of INSERT …
-
#explain(opts = OPTS) ⇒ Object
Return the results of an EXPLAIN query as a string.
-
#for_share ⇒ Object
Return a cloned dataset which will use FOR SHARE to lock returned rows.
-
#full_text_search(cols, terms, opts = OPTS) ⇒ Object
Run a full text search on PostgreSQL.
-
#insert(*values) ⇒ Object
Insert given values into the database.
-
#insert_conflict(opts = OPTS) ⇒ Object
Handle uniqueness violations when inserting, by updating the conflicting row, using ON CONFLICT.
-
#insert_ignore ⇒ Object
Ignore uniqueness/exclusion violations when inserting, using ON CONFLICT DO NOTHING.
-
#insert_select(*values) ⇒ Object
Insert a record, returning the record inserted, using RETURNING.
-
#insert_select_sql(*values) ⇒ Object
The SQL to use for an insert_select, adds a RETURNING clause to the insert unless the RETURNING clause is already present.
-
#lock(mode, opts = OPTS) ⇒ Object
Locks all tables in the dataset's FROM clause (but not in JOINs) with the specified mode (e.g. 'EXCLUSIVE').
-
#overriding_system_value ⇒ Object
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.
-
#overriding_user_value ⇒ Object
Use OVERRIDING USER VALUE for INSERT statements, so that identity columns always use the sequence value instead of the user supplied value.
- #supports_cte?(type = :select) ⇒ Boolean
-
#supports_cte_in_subqueries? ⇒ Boolean
PostgreSQL supports using the WITH clause in subqueries if it supports using WITH at all (i.e. on PostgreSQL 8.4+).
-
#supports_distinct_on? ⇒ Boolean
DISTINCT ON is a PostgreSQL extension.
-
#supports_group_cube? ⇒ Boolean
PostgreSQL 9.5+ supports GROUP CUBE.
-
#supports_group_rollup? ⇒ Boolean
PostgreSQL 9.5+ supports GROUP ROLLUP.
-
#supports_grouping_sets? ⇒ Boolean
PostgreSQL 9.5+ supports GROUPING SETS.
-
#supports_insert_conflict? ⇒ Boolean
PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.
-
#supports_insert_select? ⇒ Boolean
True unless insert returning has been disabled for this dataset.
-
#supports_lateral_subqueries? ⇒ Boolean
PostgreSQL 9.3+ supports lateral subqueries.
-
#supports_modifying_joins? ⇒ Boolean
PostgreSQL supports modifying joined datasets.
-
#supports_nowait? ⇒ Boolean
PostgreSQL supports NOWAIT.
-
#supports_regexp? ⇒ Boolean
PostgreSQL supports pattern matching via regular expressions.
-
#supports_returning?(type) ⇒ Boolean
Returning is always supported.
-
#supports_skip_locked? ⇒ Boolean
PostgreSQL 9.5+ supports SKIP LOCKED.
-
#supports_timestamp_timezones? ⇒ Boolean
PostgreSQL supports timezones in literal timestamps.
-
#supports_window_clause? ⇒ Boolean
PostgreSQL 8.4+ supports WINDOW clause.
-
#supports_window_function_frame_option?(option) ⇒ Boolean
Base support added in 8.4, offset supported added in 9.0, GROUPS and EXCLUDE support added in 11.0.
-
#supports_window_functions? ⇒ Boolean
PostgreSQL 8.4+ supports window functions.
-
#truncate(opts = OPTS) ⇒ Object
Truncates the dataset.
-
#with_ties ⇒ Object
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.
Instance Method Details
#analyze ⇒ Object
Return the results of an EXPLAIN ANALYZE query as a string
1532 1533 1534 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1532 def analyze explain(:analyze=>true) end |
#complex_expression_sql_append(sql, op, args) ⇒ Object
Handle converting the ruby xor operator (^) into the PostgreSQL xor operator (#), and use the ILIKE and NOT ILIKE operators.
1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 1549 1550 1551 1552 1553 1554 1555 1556 1557 1558 1559 1560 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1539 def complex_expression_sql_append(sql, op, args) case op when :^ j = ' # ' c = false args.each do |a| sql << j if c literal_append(sql, a) c ||= true end when :ILIKE, :'NOT ILIKE' sql << '(' literal_append(sql, args[0]) sql << ' ' << op.to_s << ' ' literal_append(sql, args[1]) sql << " ESCAPE " literal_append(sql, "\\") sql << ')' else super end end |
#disable_insert_returning ⇒ Object
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).
1576 1577 1578 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1576 def disable_insert_returning clone(:disable_insert_returning=>true) end |
#explain(opts = OPTS) ⇒ Object
Return the results of an EXPLAIN query as a string
1581 1582 1583 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1581 def explain(opts=OPTS) with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map(:'QUERY PLAN').join("\r\n") end |
#for_share ⇒ Object
Return a cloned dataset which will use FOR SHARE to lock returned rows.
1586 1587 1588 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1586 def for_share lock_style(:share) end |
#full_text_search(cols, terms, opts = OPTS) ⇒ Object
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.
1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1609 def full_text_search(cols, terms, opts = OPTS) lang = Sequel.cast(opts[:language] || 'simple', :regconfig) unless opts[:tsvector] phrase_cols = full_text_string_join(cols) cols = Sequel.function(:to_tsvector, lang, phrase_cols) end unless opts[:tsquery] phrase_terms = terms.is_a?(Array) ? terms.join(' | ') : terms query_func = case to_tsquery = opts[:to_tsquery] when :phrase, :plain :"#{to_tsquery}to_tsquery" else (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery end terms = Sequel.function(query_func, lang, phrase_terms) end ds = where(Sequel.lit(["", " @@ ", ""], cols, terms)) if opts[:phrase] raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery] ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true) end if opts[:rank] ds = ds.reverse{ts_rank_cd(cols, terms)} end if opts[:headline] ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)} end ds end |
#insert(*values) ⇒ Object
Insert given values into the database.
1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1649 def insert(*values) if @opts[:returning] # Already know which columns to return, let the standard code handle it super elsif @opts[:sql] || @opts[:disable_insert_returning] # Raw SQL used or RETURNING disabled, just use the default behavior # and return nil since sequence is not known. super nil else # Force the use of RETURNING with the primary key value, # unless it has been disabled. returning(insert_pk).insert(*values){|r| return r.values.first} end end |
#insert_conflict(opts = OPTS) ⇒ Object
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)
1700 1701 1702 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1700 def insert_conflict(opts=OPTS) clone(:insert_conflict => opts) end |
#insert_ignore ⇒ Object
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
1710 1711 1712 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1710 def insert_ignore insert_conflict end |
#insert_select(*values) ⇒ Object
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.
1717 1718 1719 1720 1721 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1717 def insert_select(*values) return unless supports_insert_select? # Handle case where query does not return a row server?(:default).with_sql_first(insert_select_sql(*values)) || false end |
#insert_select_sql(*values) ⇒ Object
The SQL to use for an insert_select, adds a RETURNING clause to the insert unless the RETURNING clause is already present.
1725 1726 1727 1728 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1725 def insert_select_sql(*values) ds = opts[:returning] ? self : returning ds.insert_sql(*values) end |
#lock(mode, opts = OPTS) ⇒ Object
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.
1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1735 def lock(mode, opts=OPTS) if block_given? # perform locking inside a transaction and yield to block @db.transaction(opts){lock(mode, opts); yield} else sql = 'LOCK TABLE '.dup source_list_append(sql, @opts[:from]) mode = mode.to_s.upcase.strip unless LOCK_MODES.include?(mode) raise Error, "Unsupported lock mode: #{mode}" end sql << " IN #{mode} MODE" @db.execute(sql, opts) end nil end |
#overriding_system_value ⇒ Object
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.
1754 1755 1756 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1754 def overriding_system_value clone(:override=>:system) end |
#overriding_user_value ⇒ Object
Use OVERRIDING USER VALUE for INSERT statements, so that identity columns always use the sequence value instead of the user supplied value.
1760 1761 1762 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1760 def overriding_user_value clone(:override=>:user) end |
#supports_cte?(type = :select) ⇒ Boolean
1764 1765 1766 1767 1768 1769 1770 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1764 def supports_cte?(type=:select) if type == :select server_version >= 80400 else server_version >= 90100 end end |
#supports_cte_in_subqueries? ⇒ Boolean
PostgreSQL supports using the WITH clause in subqueries if it supports using WITH at all (i.e. on PostgreSQL 8.4+).
1774 1775 1776 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1774 def supports_cte_in_subqueries? supports_cte? end |
#supports_distinct_on? ⇒ Boolean
DISTINCT ON is a PostgreSQL extension
1779 1780 1781 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1779 def supports_distinct_on? true end |
#supports_group_cube? ⇒ Boolean
PostgreSQL 9.5+ supports GROUP CUBE
1784 1785 1786 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1784 def supports_group_cube? server_version >= 90500 end |
#supports_group_rollup? ⇒ Boolean
PostgreSQL 9.5+ supports GROUP ROLLUP
1789 1790 1791 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1789 def supports_group_rollup? server_version >= 90500 end |
#supports_grouping_sets? ⇒ Boolean
PostgreSQL 9.5+ supports GROUPING SETS
1794 1795 1796 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1794 def supports_grouping_sets? server_version >= 90500 end |
#supports_insert_conflict? ⇒ Boolean
PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.
1804 1805 1806 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1804 def supports_insert_conflict? server_version >= 90500 end |
#supports_insert_select? ⇒ Boolean
True unless insert returning has been disabled for this dataset.
1799 1800 1801 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1799 def supports_insert_select? !@opts[:disable_insert_returning] end |
#supports_lateral_subqueries? ⇒ Boolean
PostgreSQL 9.3+ supports lateral subqueries
1809 1810 1811 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1809 def supports_lateral_subqueries? server_version >= 90300 end |
#supports_modifying_joins? ⇒ Boolean
PostgreSQL supports modifying joined datasets
1814 1815 1816 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1814 def true end |
#supports_nowait? ⇒ Boolean
PostgreSQL supports NOWAIT.
1819 1820 1821 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1819 def supports_nowait? true end |
#supports_regexp? ⇒ Boolean
PostgreSQL supports pattern matching via regular expressions
1829 1830 1831 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1829 def supports_regexp? true end |
#supports_returning?(type) ⇒ Boolean
Returning is always supported.
1824 1825 1826 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1824 def supports_returning?(type) true end |
#supports_skip_locked? ⇒ Boolean
PostgreSQL 9.5+ supports SKIP LOCKED.
1834 1835 1836 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1834 def supports_skip_locked? server_version >= 90500 end |
#supports_timestamp_timezones? ⇒ Boolean
PostgreSQL supports timezones in literal timestamps
1839 1840 1841 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1839 def true end |
#supports_window_clause? ⇒ Boolean
PostgreSQL 8.4+ supports WINDOW clause.
1844 1845 1846 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1844 def supports_window_clause? server_version >= 80400 end |
#supports_window_function_frame_option?(option) ⇒ Boolean
Base support added in 8.4, offset supported added in 9.0, GROUPS and EXCLUDE support added in 11.0.
1855 1856 1857 1858 1859 1860 1861 1862 1863 1864 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1855 def supports_window_function_frame_option?(option) case option when :rows, :range true when :offset server_version >= 90000 when :groups, :exclude server_version >= 110000 end end |
#supports_window_functions? ⇒ Boolean
PostgreSQL 8.4+ supports window functions
1849 1850 1851 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1849 def supports_window_functions? server_version >= 80400 end |
#truncate(opts = OPTS) ⇒ Object
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
1882 1883 1884 1885 1886 1887 1888 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1882 def truncate(opts = OPTS) if opts.empty? super() else clone(:truncate_opts=>opts).truncate end end |
#with_ties ⇒ Object
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.
1893 1894 1895 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1893 def with_ties clone(:limit_with_ties=>true) end |