Instance methods for datasets that connect to a PostgreSQL database.
Return the results of an EXPLAIN ANALYZE query as a string
# File lib/sequel/adapters/shared/postgres.rb, line 1131 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 1138 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
Return the results of an EXPLAIN query as a string
# File lib/sequel/adapters/shared/postgres.rb, line 1162 def explain(opts=OPTS) with_sql((opts[:analyze] ? EXPLAIN_ANALYZE : EXPLAIN) + select_sql).map(QUERY_PLAN).join(CRLF) end
PostgreSQL specific full text search syntax, using tsearch2 (included in 8.3 by default, and available for earlier versions as an add-on).
# File lib/sequel/adapters/shared/postgres.rb, line 1173 def full_text_search(cols, terms, opts = OPTS) lang = opts[:language] || 'simple' terms = terms.join(' | ') if terms.is_a?(Array) filter("to_tsvector(?::regconfig, ?) @@ to_tsquery(?::regconfig, ?)", lang, full_text_string_join(cols), lang, terms) end
Insert given values into the database.
# File lib/sequel/adapters/shared/postgres.rb, line 1180 def insert(*values) if @opts[:returning] # already know which columns to return, let the standard code # handle it super elsif @opts[:sql] # raw SQL used, so don't know which table is being inserted # into, and therefore can't determine primary key. Run the # insert statement and return nil. super nil else # Force the use of RETURNING with the primary key value. returning(insert_pk).insert(*values){|r| return r.values.first} end end
Insert a record returning the record inserted
# File lib/sequel/adapters/shared/postgres.rb, line 1198 def insert_select(*values) returning.insert(*values){|r| return r} 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 1207 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 ' 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
PostgreSQL allows inserting multiple rows at once.
# File lib/sequel/adapters/shared/postgres.rb, line 1224 def multi_insert_sql(columns, values) sql = LiteralString.new('VALUES ') expression_list_append(sql, values.map{|r| Array(r)}) [insert_sql(columns, sql)] 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 1232 def supports_cte_in_subqueries? supports_cte? end
DISTINCT ON is a PostgreSQL extension
# File lib/sequel/adapters/shared/postgres.rb, line 1237 def supports_distinct_on? true end
PostgreSQL supports modifying joined datasets
# File lib/sequel/adapters/shared/postgres.rb, line 1242 def supports_modifying_joins? true end
PostgreSQL supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/postgres.rb, line 1252 def supports_regexp? true end
Returning is always supported.
# File lib/sequel/adapters/shared/postgres.rb, line 1247 def supports_returning?(type) true end
PostgreSQL supports timezones in literal timestamps
# File lib/sequel/adapters/shared/postgres.rb, line 1257 def supports_timestamp_timezones? true end
PostgreSQL 8.4+ supports window functions
# File lib/sequel/adapters/shared/postgres.rb, line 1262 def supports_window_functions? server_version >= 80400 end
Truncates the dataset. Returns nil.
Options:
whether to use the CASCADE option, useful when truncating
tables with Foreign Keys.
truncate using ONLY, so child tables are unaffected
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 1281 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 1290 def window(name, opts) clone(:window=>(@opts[:window]||[]) + [[name, SQL::Window.new(opts)]]) end
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 1300 def _import(columns, values, opts=OPTS) if @opts[:returning] statements = multi_insert_sql(columns, values) @db.transaction(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
Format TRUNCATE statement with PostgreSQL specific options.
# File lib/sequel/adapters/shared/postgres.rb, line 1316 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 1322 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 1415 def compound_dataset_sql_append(sql, ds) sql << PAREN_OPEN super sql << PAREN_CLOSE end
PostgreSQL allows deleting from joined datasets
# File lib/sequel/adapters/shared/postgres.rb, line 1328 def delete_clause_methods if server_version >= 90100 DELETE_CLAUSE_METHODS_91 else DELETE_CLAUSE_METHODS end end
Only include the primary table in the main delete clause
# File lib/sequel/adapters/shared/postgres.rb, line 1337 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 1343 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 1454 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
PostgreSQL allows a RETURNING clause.
# File lib/sequel/adapters/shared/postgres.rb, line 1348 def insert_clause_methods if server_version >= 90100 INSERT_CLAUSE_METHODS_91 else INSERT_CLAUSE_METHODS end end
Return the primary key to use for RETURNING in an INSERT statement
# File lib/sequel/adapters/shared/postgres.rb, line 1357 def insert_pk if (f = opts[:from]) && !f.empty? && (pk = db.primary_key(f.first)) Sequel::SQL::Identifier.new(pk) end end
For multiple table support, PostgreSQL requires at least two from tables, with joins allowed.
# File lib/sequel/adapters/shared/postgres.rb, line 1365 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 1376 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 1381 def literal_false BOOL_FALSE end
PostgreSQL quotes NaN and Infinity.
# File lib/sequel/adapters/shared/postgres.rb, line 1386 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 1399 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 1404 def literal_true BOOL_TRUE end
The order of clauses in the SELECT SQL statement
# File lib/sequel/adapters/shared/postgres.rb, line 1409 def select_clause_methods server_version >= 80400 ? SELECT_CLAUSE_METHODS_84 : SELECT_CLAUSE_METHODS end
Support FOR SHARE locking when using the :share lock style.
# File lib/sequel/adapters/shared/postgres.rb, line 1422 def select_lock_sql(sql) @opts[:lock] == :share ? (sql << FOR_SHARE) : super end
SQL fragment for named window specifications
# File lib/sequel/adapters/shared/postgres.rb, line 1427 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 1444 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 1449 def server_version db.server_version(@opts[:server]) end
PostgreSQL splits the main table from the joined tables
# File lib/sequel/adapters/shared/postgres.rb, line 1462 def update_clause_methods if server_version >= 90100 UPDATE_CLAUSE_METHODS_91 else UPDATE_CLAUSE_METHODS end end
Use FROM to specify additional tables in an update query
# File lib/sequel/adapters/shared/postgres.rb, line 1471 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 1476 def update_table_sql(sql) sql << SPACE source_list_append(sql, @opts[:from][0..0]) end