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 914 914: def analyze 915: explain(:analyze=>true) 916: end
Handle converting the ruby xor operator (^) into the PostgreSQL xor operator (#).
# File lib/sequel/adapters/shared/postgres.rb, line 920 920: def complex_expression_sql_append(sql, op, args) 921: case op 922: when :^ 923: j = XOR_OP 924: c = false 925: args.each do |a| 926: sql << j if c 927: literal_append(sql, a) 928: c ||= true 929: end 930: else 931: super 932: end 933: end
Return the results of an EXPLAIN query as a string
# File lib/sequel/adapters/shared/postgres.rb, line 936 936: def explain(opts={}) 937: with_sql((opts[:analyze] ? EXPLAIN_ANALYZE : EXPLAIN) + select_sql).map(QUERY_PLAN).join(CRLF) 938: 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 947 947: def full_text_search(cols, terms, opts = {}) 948: lang = opts[:language] || 'simple' 949: terms = terms.join(' | ') if terms.is_a?(Array) 950: filter("to_tsvector(?::regconfig, ?) @@ to_tsquery(?::regconfig, ?)", lang, full_text_string_join(cols), lang, terms) 951: end
Insert given values into the database.
# File lib/sequel/adapters/shared/postgres.rb, line 954 954: def insert(*values) 955: if @opts[:returning] 956: # already know which columns to return, let the standard code 957: # handle it 958: super 959: elsif @opts[:sql] 960: # raw SQL used, so don't know which table is being inserted 961: # into, and therefore can't determine primary key. Run the 962: # insert statement and return nil. 963: super 964: nil 965: else 966: # Force the use of RETURNING with the primary key value. 967: returning(insert_pk).insert(*values){|r| return r.values.first} 968: end 969: end
Insert a record returning the record inserted
# File lib/sequel/adapters/shared/postgres.rb, line 972 972: def insert_select(*values) 973: returning.insert(*values){|r| return r} 974: 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 981 981: def lock(mode, opts={}) 982: if block_given? # perform locking inside a transaction and yield to block 983: @db.transaction(opts){lock(mode, opts); yield} 984: else 985: @db.execute(LOCK % [source_list(@opts[:from]), mode], opts) # lock without a transaction 986: end 987: nil 988: end
PostgreSQL allows inserting multiple rows at once.
# File lib/sequel/adapters/shared/postgres.rb, line 991 991: def multi_insert_sql(columns, values) 992: sql = LiteralString.new('VALUES ') 993: expression_list_append(sql, values.map{|r| Array(r)}) 994: [insert_sql(columns, sql)] 995: 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 999 999: def supports_cte_in_subqueries? 1000: supports_cte? 1001: end
DISTINCT ON is a PostgreSQL extension
# File lib/sequel/adapters/shared/postgres.rb, line 1004 1004: def supports_distinct_on? 1005: true 1006: end
PostgreSQL supports modifying joined datasets
# File lib/sequel/adapters/shared/postgres.rb, line 1009 1009: def supports_modifying_joins? 1010: true 1011: end
Returning is always supported.
# File lib/sequel/adapters/shared/postgres.rb, line 1014 1014: def supports_returning?(type) 1015: true 1016: end
PostgreSQL supports timezones in literal timestamps
# File lib/sequel/adapters/shared/postgres.rb, line 1019 1019: def supports_timestamp_timezones? 1020: true 1021: 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 1043 1043: def truncate(opts = {}) 1044: if opts.empty? 1045: super() 1046: else 1047: clone(:truncate_opts=>opts).truncate 1048: end 1049: 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 1062 1062: def _import(columns, values, opts={}) 1063: if @opts[:returning] 1064: statements = multi_insert_sql(columns, values) 1065: @db.transaction(opts.merge(:server=>@opts[:server])) do 1066: statements.map{|st| returning_fetch_rows(st)} 1067: end.first.map{|v| v.length == 1 ? v.values.first : v} 1068: elsif opts[:return] == :primary_key 1069: returning(insert_pk)._import(columns, values, opts) 1070: else 1071: super 1072: end 1073: end