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 910 def analyze explain(:analyze=>true) end
Handle converting the ruby xor operator (^) into the PostgreSQL xor operator (#).
# File lib/sequel/adapters/shared/postgres.rb, line 916 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 else super end end
Return the results of an EXPLAIN query as a string
# File lib/sequel/adapters/shared/postgres.rb, line 932 def explain(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 943 def full_text_search(cols, terms, 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 950 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 968 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 977 def lock(mode, opts={}) if block_given? # perform locking inside a transaction and yield to block @db.transaction(opts){lock(mode, opts); yield} else @db.execute(LOCK % [source_list(@opts[:from]), mode], opts) # lock without a transaction end nil end
PostgreSQL allows inserting multiple rows at once.
# File lib/sequel/adapters/shared/postgres.rb, line 987 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 995 def supports_cte_in_subqueries? supports_cte? end
DISTINCT ON is a PostgreSQL extension
# File lib/sequel/adapters/shared/postgres.rb, line 1000 def supports_distinct_on? true end
PostgreSQL supports modifying joined datasets
# File lib/sequel/adapters/shared/postgres.rb, line 1005 def supports_modifying_joins? true end
Returning is always supported.
# File lib/sequel/adapters/shared/postgres.rb, line 1010 def supports_returning?(type) true end
PostgreSQL supports timezones in literal timestamps
# File lib/sequel/adapters/shared/postgres.rb, line 1015 def supports_timestamp_timezones? true end
PostgreSQL 8.4+ supports window functions
# File lib/sequel/adapters/shared/postgres.rb, line 1020 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 1039 def truncate(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 1048 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 1058 def _import(columns, values, 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.