module Sequel::Postgres::DatasetMethods
Instance methods for datasets that connect to a PostgreSQL database.
Constants
- ACCESS_EXCLUSIVE
- ACCESS_SHARE
- APOS
- APOS_RE
- AS
- BACKSLASH
- BLOB_RE
- BOOL_FALSE
- BOOL_TRUE
- COMMA
- COMMA_SEPARATOR
- CRLF
- DOUBLE_APOS
- EMPTY_STRING
- ESCAPE
- EXCLUSIVE
- EXPLAIN
- EXPLAIN_ANALYZE
- FOR_SHARE
- FROM
- LOCK_MODES
- NON_SQL_OPTIONS
- NULL
- PAREN_CLOSE
- PAREN_OPEN
- PG_TIMESTAMP_FORMAT
- QUERY_PLAN
- ROW_EXCLUSIVE
- ROW_SHARE
- SELECT_VALUES
- SHARE
- SHARE_ROW_EXCLUSIVE
- SHARE_UPDATE_EXCLUSIVE
- SKIP_LOCKED
- SPACE
- SQL_WITH_RECURSIVE
- WINDOW
- XOR_OP
Public Instance Methods
Return the results of an EXPLAIN ANALYZE query as a string
# File lib/sequel/adapters/shared/postgres.rb, line 1284 def analyze explain(:analyze=>true) 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, line 1291 def complex_expression_sql_append(sql, op, args) case op when :^ j = XOR_OP c = false args.each do |a| sql << j if c literal_append(sql, a) c ||= true end when :ILIKE, :'NOT ILIKE' sql << PAREN_OPEN literal_append(sql, args.at(0)) sql << SPACE << op.to_s << SPACE literal_append(sql, args.at(1)) sql << ESCAPE literal_append(sql, BACKSLASH) sql << PAREN_CLOSE else super end 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, line 1328 def disable_insert_returning clone(:disable_insert_returning=>true) end
Return the results of an EXPLAIN query as a string
# File lib/sequel/adapters/shared/postgres.rb, line 1333 def explain(opts=OPTS) with_sql((opts[:analyze] ? EXPLAIN_ANALYZE : EXPLAIN) + select_sql).map(QUERY_PLAN).join(CRLF) 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, line 1361 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 given values into the database.
# File lib/sequel/adapters/shared/postgres.rb, line 1401 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
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=>: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=>:excluded__b}, :update_where=>{: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, line 1452 def insert_conflict(opts=OPTS) clone(:insert_conflict => opts) 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, line 1462 def insert_ignore insert_conflict end
Insert a record returning the record inserted. Always returns nil without inserting a query if #disable_insert_returning is used.
# File lib/sequel/adapters/shared/postgres.rb, line 1468 def insert_select(*values) return unless supports_insert_select? server?(:default).with_sql_first(insert_select_sql(*values)) 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, line 1475 def insert_select_sql(*values) ds = opts[:returning] ? self : returning ds.insert_sql(*values) 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, line 1485 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
# File lib/sequel/adapters/shared/postgres.rb, line 1501 def supports_cte?(type=:select) if type == :select server_version >= 80400 else server_version >= 90100 end 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, line 1511 def supports_cte_in_subqueries? supports_cte? end
DISTINCT ON is a PostgreSQL extension
# File lib/sequel/adapters/shared/postgres.rb, line 1516 def supports_distinct_on? true end
PostgreSQL 9.5+ supports GROUP CUBE
# File lib/sequel/adapters/shared/postgres.rb, line 1521 def supports_group_cube? server_version >= 90500 end
PostgreSQL 9.5+ supports GROUP ROLLUP
# File lib/sequel/adapters/shared/postgres.rb, line 1526 def supports_group_rollup? server_version >= 90500 end
PostgreSQL 9.5+ supports GROUPING SETS
# File lib/sequel/adapters/shared/postgres.rb, line 1531 def supports_grouping_sets? server_version >= 90500 end
PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.
# File lib/sequel/adapters/shared/postgres.rb, line 1541 def supports_insert_conflict? server_version >= 90500 end
True unless insert returning has been disabled for this dataset.
# File lib/sequel/adapters/shared/postgres.rb, line 1536 def supports_insert_select? !@opts[:disable_insert_returning] end
PostgreSQL 9.3rc1+ supports lateral subqueries
# File lib/sequel/adapters/shared/postgres.rb, line 1546 def supports_lateral_subqueries? server_version >= 90300 end
PostgreSQL supports modifying joined datasets
# File lib/sequel/adapters/shared/postgres.rb, line 1551 def supports_modifying_joins? true end
PostgreSQL supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/postgres.rb, line 1561 def supports_regexp? true end
Returning is always supported.
# File lib/sequel/adapters/shared/postgres.rb, line 1556 def supports_returning?(type) true end
PostgreSQL 9.5+ supports SKIP LOCKED.
# File lib/sequel/adapters/shared/postgres.rb, line 1566 def supports_skip_locked? server_version >= 90500 end
PostgreSQL supports timezones in literal timestamps
# File lib/sequel/adapters/shared/postgres.rb, line 1571 def supports_timestamp_timezones? true end
PostgreSQL 8.4+ supports window functions
# File lib/sequel/adapters/shared/postgres.rb, line 1576 def supports_window_functions? server_version >= 80400 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" # => nil DB[:table].truncate(:cascade => true, :only=>true, :restart=>true) # TRUNCATE TABLE ONLY "table" RESTART IDENTITY CASCADE # => nil
# File lib/sequel/adapters/shared/postgres.rb, line 1595 def truncate(opts = OPTS) if opts.empty? super() else clone(:truncate_opts=>opts).truncate end end
Return a clone of the dataset with an addition named window that can be referenced in window functions.
# File lib/sequel/adapters/shared/postgres.rb, line 1604 def window(name, opts) clone(:window=>(@opts[:window]||[]) + [[name, SQL::Window.new(opts)]]) 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, line 1614 def _import(columns, values, opts=OPTS) if @opts[:returning] statements = multi_insert_sql(columns, values) @db.transaction(Hash[opts].merge!(:server=>@opts[:server])) do statements.map{|st| returning_fetch_rows(st)} end.first.map{|v| v.length == 1 ? v.values.first : v} elsif opts[:return] == :primary_key returning(insert_pk)._import(columns, values, opts) else super end end
Private Instance Methods
Format TRUNCATE statement with PostgreSQL specific options.
# File lib/sequel/adapters/shared/postgres.rb, line 1630 def _truncate_sql(table) to = @opts[:truncate_opts] || {} "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}" end
Allow truncation of multiple source tables.
# File lib/sequel/adapters/shared/postgres.rb, line 1636 def check_truncation_allowed! raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group] raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join] 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, line 1751 def compound_dataset_sql_append(sql, ds) sql << PAREN_OPEN super sql << PAREN_CLOSE end
Only include the primary table in the main delete clause
# File lib/sequel/adapters/shared/postgres.rb, line 1642 def delete_from_sql(sql) sql << FROM source_list_append(sql, @opts[:from][0..0]) end
Use USING to specify additional tables in a delete query
# File lib/sequel/adapters/shared/postgres.rb, line 1648 def delete_using_sql(sql) join_from_sql(:USING, sql) end
Concatenate the expressions with a space in between
# File lib/sequel/adapters/shared/postgres.rb, line 1818 def full_text_string_join(cols) cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, EMPTY_STRING)} cols = cols.zip([SPACE] * cols.length).flatten cols.pop SQL::StringExpression.new(:'||', *cols) end
Add ON CONFLICT clause if it should be used
# File lib/sequel/adapters/shared/postgres.rb, line 1653 def insert_conflict_sql(sql) if opts = @opts[:insert_conflict] sql << " ON CONFLICT" if target = opts[:constraint] sql << " ON CONSTRAINT " identifier_append(sql, target) elsif target = opts[:target] sql << ' ' identifier_append(sql, Array(target)) if conflict_where = opts[:conflict_where] sql << " WHERE " literal_append(sql, conflict_where) end end if values = opts[:update] sql << " DO UPDATE SET " update_sql_values_hash(sql, values) if update_where = opts[:update_where] sql << " WHERE " literal_append(sql, update_where) end else sql << " DO NOTHING" end end end
Return the primary key to use for RETURNING in an INSERT statement
# File lib/sequel/adapters/shared/postgres.rb, line 1683 def insert_pk if (f = opts[:from]) && !f.empty? case t = f.first when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier if pk = db.primary_key(t) Sequel::SQL::Identifier.new(pk) end end end end
For multiple table support, PostgreSQL requires at least two from tables, with joins allowed.
# File lib/sequel/adapters/shared/postgres.rb, line 1696 def join_from_sql(type, sql) if(from = @opts[:from][1..-1]).empty? raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join] else sql << SPACE << type.to_s << SPACE source_list_append(sql, from) select_join_sql(sql) end end
Use a generic blob quoting method, hopefully overridden in one of the subadapter methods
# File lib/sequel/adapters/shared/postgres.rb, line 1707 def literal_blob_append(sql, v) sql << APOS << v.gsub(BLOB_RE){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << APOS end
PostgreSQL uses FALSE for false values
# File lib/sequel/adapters/shared/postgres.rb, line 1712 def literal_false BOOL_FALSE end
PostgreSQL quotes NaN and Infinity.
# File lib/sequel/adapters/shared/postgres.rb, line 1717 def literal_float(value) if value.finite? super elsif value.nan? "'NaN'" elsif value.infinite? == 1 "'Infinity'" else "'-Infinity'" end end
Assume that SQL standard quoting is on, per Sequel's defaults
# File lib/sequel/adapters/shared/postgres.rb, line 1730 def literal_string_append(sql, v) sql << APOS << v.gsub(APOS_RE, DOUBLE_APOS) << APOS end
PostgreSQL uses FALSE for false values
# File lib/sequel/adapters/shared/postgres.rb, line 1735 def literal_true BOOL_TRUE end
PostgreSQL supports multiple rows in INSERT.
# File lib/sequel/adapters/shared/postgres.rb, line 1740 def multi_insert_sql_strategy :values 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, line 1759 def select_lock_sql(sql) if @opts[:lock] == :share sql << FOR_SHARE else super end if @opts[:skip_locked] sql << SKIP_LOCKED end end
Support VALUES clause instead of the SELECT clause to return rows.
# File lib/sequel/adapters/shared/postgres.rb, line 1772 def select_values_sql(sql) sql << SELECT_VALUES expression_list_append(sql, opts[:values]) end
SQL fragment for named window specifications
# File lib/sequel/adapters/shared/postgres.rb, line 1778 def select_window_sql(sql) if ws = @opts[:window] sql << WINDOW c = false co = COMMA as = AS ws.map do |name, window| sql << co if c literal_append(sql, name) sql << as literal_append(sql, window) c ||= true end end end
Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive
# File lib/sequel/adapters/shared/postgres.rb, line 1795 def select_with_sql_base opts[:with].any?{|w| w[:recursive]} ? SQL_WITH_RECURSIVE : super end
The version of the database server
# File lib/sequel/adapters/shared/postgres.rb, line 1800 def server_version db.server_version(@opts[:server]) end
PostgreSQL supports quoted function names.
# File lib/sequel/adapters/shared/postgres.rb, line 1805 def supports_quoted_function_names? true end
# File lib/sequel/adapters/shared/postgres.rb, line 1809 def to_prepared_statement(type, *a) if type == :insert && !@opts.has_key?(:returning) returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a) else super end end
Use FROM to specify additional tables in an update query
# File lib/sequel/adapters/shared/postgres.rb, line 1826 def update_from_sql(sql) join_from_sql(:FROM, sql) end
Only include the primary table in the main update clause
# File lib/sequel/adapters/shared/postgres.rb, line 1831 def update_table_sql(sql) sql << SPACE source_list_append(sql, @opts[:from][0..0]) end