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 1120 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 1127 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 1151 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 1162 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 1169 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 1187 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 1196 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 1213 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 1221 def supports_cte_in_subqueries? supports_cte? end
DISTINCT ON is a PostgreSQL extension
# File lib/sequel/adapters/shared/postgres.rb, line 1226 def supports_distinct_on? true end
PostgreSQL supports modifying joined datasets
# File lib/sequel/adapters/shared/postgres.rb, line 1231 def supports_modifying_joins? true end
PostgreSQL supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/postgres.rb, line 1241 def supports_regexp? true end
Returning is always supported.
# File lib/sequel/adapters/shared/postgres.rb, line 1236 def supports_returning?(type) true end
PostgreSQL supports timezones in literal timestamps
# File lib/sequel/adapters/shared/postgres.rb, line 1246 def supports_timestamp_timezones? true end
PostgreSQL 8.4+ supports window functions
# File lib/sequel/adapters/shared/postgres.rb, line 1251 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 1270 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 1279 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 1289 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
Generated with the Darkfish Rdoc Generator 2.