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') |
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 |
ESCAPE | = | Dataset::ESCAPE |
BACKSLASH | = | Dataset::BACKSLASH |
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 |
LOCK_MODES | = | ['ACCESS SHARE', 'ROW SHARE', 'ROW EXCLUSIVE', 'SHARE UPDATE EXCLUSIVE', 'SHARE', 'SHARE ROW EXCLUSIVE', 'EXCLUSIVE', 'ACCESS EXCLUSIVE'].each{|s| s.freeze} |
Return the results of an EXPLAIN ANALYZE query as a string
# File lib/sequel/adapters/shared/postgres.rb, line 1135 1135: def analyze 1136: explain(:analyze=>true) 1137: 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 1142 1142: def complex_expression_sql_append(sql, op, args) 1143: case op 1144: when :^ 1145: j = XOR_OP 1146: c = false 1147: args.each do |a| 1148: sql << j if c 1149: literal_append(sql, a) 1150: c ||= true 1151: end 1152: when :ILIKE, 'NOT ILIKE''NOT ILIKE' 1153: sql << PAREN_OPEN 1154: literal_append(sql, args.at(0)) 1155: sql << SPACE << op.to_s << SPACE 1156: literal_append(sql, args.at(1)) 1157: sql << ESCAPE 1158: literal_append(sql, BACKSLASH) 1159: sql << PAREN_CLOSE 1160: else 1161: super 1162: end 1163: end
Return the results of an EXPLAIN query as a string
# File lib/sequel/adapters/shared/postgres.rb, line 1166 1166: def explain(opts=OPTS) 1167: with_sql((opts[:analyze] ? EXPLAIN_ANALYZE : EXPLAIN) + select_sql).map(QUERY_PLAN).join(CRLF) 1168: 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 1177 1177: def full_text_search(cols, terms, opts = OPTS) 1178: lang = opts[:language] || 'simple' 1179: terms = terms.join(' | ') if terms.is_a?(Array) 1180: filter("to_tsvector(?::regconfig, ?) @@ to_tsquery(?::regconfig, ?)", lang, full_text_string_join(cols), lang, terms) 1181: end
Insert given values into the database.
# File lib/sequel/adapters/shared/postgres.rb, line 1184 1184: def insert(*values) 1185: if @opts[:returning] 1186: # already know which columns to return, let the standard code 1187: # handle it 1188: super 1189: elsif @opts[:sql] 1190: # raw SQL used, so don't know which table is being inserted 1191: # into, and therefore can't determine primary key. Run the 1192: # insert statement and return nil. 1193: super 1194: nil 1195: else 1196: # Force the use of RETURNING with the primary key value. 1197: returning(insert_pk).insert(*values){|r| return r.values.first} 1198: end 1199: end
Insert a record returning the record inserted
# File lib/sequel/adapters/shared/postgres.rb, line 1202 1202: def insert_select(*values) 1203: returning.insert(*values){|r| return r} 1204: 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 1211 1211: def lock(mode, opts=OPTS) 1212: if block_given? # perform locking inside a transaction and yield to block 1213: @db.transaction(opts){lock(mode, opts); yield} 1214: else 1215: sql = 'LOCK TABLE ' 1216: source_list_append(sql, @opts[:from]) 1217: mode = mode.to_s.upcase.strip 1218: unless LOCK_MODES.include?(mode) 1219: raise Error, "Unsupported lock mode: #{mode}" 1220: end 1221: sql << " IN #{mode} MODE" 1222: @db.execute(sql, opts) 1223: end 1224: nil 1225: end
PostgreSQL allows inserting multiple rows at once.
# File lib/sequel/adapters/shared/postgres.rb, line 1228 1228: def multi_insert_sql(columns, values) 1229: sql = LiteralString.new('VALUES ') 1230: expression_list_append(sql, values.map{|r| Array(r)}) 1231: [insert_sql(columns, sql)] 1232: 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 1236 1236: def supports_cte_in_subqueries? 1237: supports_cte? 1238: end
DISTINCT ON is a PostgreSQL extension
# File lib/sequel/adapters/shared/postgres.rb, line 1241 1241: def supports_distinct_on? 1242: true 1243: end
PostgreSQL supports modifying joined datasets
# File lib/sequel/adapters/shared/postgres.rb, line 1246 1246: def supports_modifying_joins? 1247: true 1248: end
PostgreSQL supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/postgres.rb, line 1256 1256: def supports_regexp? 1257: true 1258: end
Returning is always supported.
# File lib/sequel/adapters/shared/postgres.rb, line 1251 1251: def supports_returning?(type) 1252: true 1253: end
PostgreSQL supports timezones in literal timestamps
# File lib/sequel/adapters/shared/postgres.rb, line 1261 1261: def supports_timestamp_timezones? 1262: true 1263: 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 1285 1285: def truncate(opts = OPTS) 1286: if opts.empty? 1287: super() 1288: else 1289: clone(:truncate_opts=>opts).truncate 1290: end 1291: 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 1304 1304: def _import(columns, values, opts=OPTS) 1305: if @opts[:returning] 1306: statements = multi_insert_sql(columns, values) 1307: @db.transaction(opts.merge(:server=>@opts[:server])) do 1308: statements.map{|st| returning_fetch_rows(st)} 1309: end.first.map{|v| v.length == 1 ? v.values.first : v} 1310: elsif opts[:return] == :primary_key 1311: returning(insert_pk)._import(columns, values, opts) 1312: else 1313: super 1314: end 1315: end