Module | Sequel::Postgres::DatasetMethods |
In: |
lib/sequel/adapters/shared/postgres.rb
|
Instance methods for datasets that connect to a PostgreSQL database.
ACCESS_SHARE | = | 'ACCESS SHARE'.freeze |
ACCESS_EXCLUSIVE | = | 'ACCESS EXCLUSIVE'.freeze |
BOOL_FALSE | = | 'false'.freeze |
BOOL_TRUE | = | 'true'.freeze |
COMMA_SEPARATOR | = | ', '.freeze |
DELETE_CLAUSE_METHODS | = | Dataset.clause_methods(:delete, %w'delete from using where returning') |
DELETE_CLAUSE_METHODS_91 | = | Dataset.clause_methods(:delete, %w'with delete from using where returning') |
EXCLUSIVE | = | 'EXCLUSIVE'.freeze |
EXPLAIN | = | 'EXPLAIN '.freeze |
EXPLAIN_ANALYZE | = | 'EXPLAIN ANALYZE '.freeze |
FOR_SHARE | = | ' FOR SHARE'.freeze |
INSERT_CLAUSE_METHODS | = | Dataset.clause_methods(:insert, %w'insert into columns values returning') |
INSERT_CLAUSE_METHODS_91 | = | Dataset.clause_methods(:insert, %w'with insert into columns values returning') |
LOCK | = | 'LOCK TABLE %s IN %s MODE'.freeze |
NULL | = | LiteralString.new('NULL').freeze |
PG_TIMESTAMP_FORMAT | = | "TIMESTAMP '%Y-%m-%d %H:%M:%S".freeze |
QUERY_PLAN | = | 'QUERY PLAN'.to_sym |
ROW_EXCLUSIVE | = | 'ROW EXCLUSIVE'.freeze |
ROW_SHARE | = | 'ROW SHARE'.freeze |
SELECT_CLAUSE_METHODS | = | Dataset.clause_methods(:select, %w'select distinct columns from join where group having compounds order limit lock') |
SELECT_CLAUSE_METHODS_84 | = | Dataset.clause_methods(:select, %w'with select distinct columns from join where group having window compounds order limit lock') |
SHARE | = | 'SHARE'.freeze |
SHARE_ROW_EXCLUSIVE | = | 'SHARE ROW EXCLUSIVE'.freeze |
SHARE_UPDATE_EXCLUSIVE | = | 'SHARE UPDATE EXCLUSIVE'.freeze |
SQL_WITH_RECURSIVE | = | "WITH RECURSIVE ".freeze |
UPDATE_CLAUSE_METHODS | = | Dataset.clause_methods(:update, %w'update table set from where returning') |
UPDATE_CLAUSE_METHODS_91 | = | Dataset.clause_methods(:update, %w'with update table set from where returning') |
SPACE | = | Dataset::SPACE |
FROM | = | Dataset::FROM |
APOS | = | Dataset::APOS |
APOS_RE | = | Dataset::APOS_RE |
DOUBLE_APOS | = | Dataset::DOUBLE_APOS |
PAREN_OPEN | = | Dataset::PAREN_OPEN |
PAREN_CLOSE | = | Dataset::PAREN_CLOSE |
COMMA | = | Dataset::COMMA |
AS | = | Dataset::AS |
XOR_OP | = | ' # '.freeze |
CRLF | = | "\r\n".freeze |
BLOB_RE | = | /[\000-\037\047\134\177-\377]/n.freeze |
WINDOW | = | " WINDOW ".freeze |
EMPTY_STRING | = | ''.freeze |
Return the results of an EXPLAIN ANALYZE query as a string
# File lib/sequel/adapters/shared/postgres.rb, line 802 802: def analyze 803: explain(:analyze=>true) 804: end
Handle converting the ruby xor operator (^) into the PostgreSQL xor operator (#).
# File lib/sequel/adapters/shared/postgres.rb, line 808 808: def complex_expression_sql_append(sql, op, args) 809: case op 810: when :^ 811: j = XOR_OP 812: c = false 813: args.each do |a| 814: sql << j if c 815: literal_append(sql, a) 816: c ||= true 817: end 818: else 819: super 820: end 821: end
Return the results of an EXPLAIN query as a string
# File lib/sequel/adapters/shared/postgres.rb, line 824 824: def explain(opts={}) 825: with_sql((opts[:analyze] ? EXPLAIN_ANALYZE : EXPLAIN) + select_sql).map(QUERY_PLAN).join(CRLF) 826: 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 835 835: def full_text_search(cols, terms, opts = {}) 836: lang = opts[:language] || 'simple' 837: terms = terms.join(' | ') if terms.is_a?(Array) 838: filter("to_tsvector(?::regconfig, ?) @@ to_tsquery(?::regconfig, ?)", lang, full_text_string_join(cols), lang, terms) 839: end
Insert given values into the database.
# File lib/sequel/adapters/shared/postgres.rb, line 842 842: def insert(*values) 843: if @opts[:returning] 844: # already know which columns to return, let the standard code 845: # handle it 846: super 847: elsif @opts[:sql] 848: # raw SQL used, so don't know which table is being inserted 849: # into, and therefore can't determine primary key. Run the 850: # insert statement and return nil. 851: super 852: nil 853: else 854: # Force the use of RETURNING with the primary key value. 855: returning(insert_pk).insert(*values){|r| return r.values.first} 856: end 857: end
Insert a record returning the record inserted
# File lib/sequel/adapters/shared/postgres.rb, line 860 860: def insert_select(*values) 861: returning.insert(*values){|r| return r} 862: 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 869 869: def lock(mode, opts={}) 870: if block_given? # perform locking inside a transaction and yield to block 871: @db.transaction(opts){lock(mode, opts); yield} 872: else 873: @db.execute(LOCK % [source_list(@opts[:from]), mode], opts) # lock without a transaction 874: end 875: nil 876: end
PostgreSQL allows inserting multiple rows at once.
# File lib/sequel/adapters/shared/postgres.rb, line 879 879: def multi_insert_sql(columns, values) 880: sql = LiteralString.new('VALUES ') 881: expression_list_append(sql, values.map{|r| Array(r)}) 882: [insert_sql(columns, sql)] 883: 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 887 887: def supports_cte_in_subqueries? 888: supports_cte? 889: end
DISTINCT ON is a PostgreSQL extension
# File lib/sequel/adapters/shared/postgres.rb, line 892 892: def supports_distinct_on? 893: true 894: end
PostgreSQL supports modifying joined datasets
# File lib/sequel/adapters/shared/postgres.rb, line 897 897: def supports_modifying_joins? 898: true 899: end
Returning is always supported.
# File lib/sequel/adapters/shared/postgres.rb, line 902 902: def supports_returning?(type) 903: true 904: end
PostgreSQL supports timezones in literal timestamps
# File lib/sequel/adapters/shared/postgres.rb, line 907 907: def supports_timestamp_timezones? 908: true 909: 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 931 931: def truncate(opts = {}) 932: if opts.empty? 933: super() 934: else 935: clone(:truncate_opts=>opts).truncate 936: end 937: 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 950 950: def _import(columns, values, opts={}) 951: if @opts[:returning] 952: statements = multi_insert_sql(columns, values) 953: @db.transaction(opts.merge(:server=>@opts[:server])) do 954: statements.map{|st| returning_fetch_rows(st)} 955: end.first.map{|v| v.length == 1 ? v.values.first : v} 956: elsif opts[:return] == :primary_key 957: returning(insert_pk)._import(columns, values, opts) 958: else 959: super 960: end 961: end