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 |
EXCLUSIVE | = | 'EXCLUSIVE'.freeze |
EXPLAIN | = | 'EXPLAIN '.freeze |
EXPLAIN_ANALYZE | = | 'EXPLAIN ANALYZE '.freeze |
FOR_SHARE | = | ' FOR SHARE'.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 |
SHARE | = | 'SHARE'.freeze |
SHARE_ROW_EXCLUSIVE | = | 'SHARE ROW EXCLUSIVE'.freeze |
SHARE_UPDATE_EXCLUSIVE | = | 'SHARE UPDATE EXCLUSIVE'.freeze |
SQL_WITH_RECURSIVE | = | "WITH RECURSIVE ".freeze |
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 |
SELECT_VALUES | = | "VALUES ".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 1222 1222: def analyze 1223: explain(:analyze=>true) 1224: 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 1229 1229: def complex_expression_sql_append(sql, op, args) 1230: case op 1231: when :^ 1232: j = XOR_OP 1233: c = false 1234: args.each do |a| 1235: sql << j if c 1236: literal_append(sql, a) 1237: c ||= true 1238: end 1239: when :ILIKE, 'NOT ILIKE''NOT ILIKE' 1240: sql << PAREN_OPEN 1241: literal_append(sql, args.at(0)) 1242: sql << SPACE << op.to_s << SPACE 1243: literal_append(sql, args.at(1)) 1244: sql << ESCAPE 1245: literal_append(sql, BACKSLASH) 1246: sql << PAREN_CLOSE 1247: else 1248: super 1249: end 1250: end
Disables automatic use of INSERT … RETURNING. You can still use returning manually to force the use of RETURNING when inserting.
This is designed for cases where INSERT RETURNING cannot be used, such as when you are using partitioning with trigger functions or conditional rules, or when you are using a PostgreSQL version less than 8.2, or a PostgreSQL derivative that does not support returning.
Note that when this method is used, insert will not return the primary key of the inserted row, you will have to get the primary key of the inserted row before inserting via nextval, or after inserting via currval or lastval (making sure to use the same database connection for currval or lastval).
# File lib/sequel/adapters/shared/postgres.rb, line 1266 1266: def disable_insert_returning 1267: clone(:disable_insert_returning=>true) 1268: end
Return the results of an EXPLAIN query as a string
# File lib/sequel/adapters/shared/postgres.rb, line 1271 1271: def explain(opts=OPTS) 1272: with_sql((opts[:analyze] ? EXPLAIN_ANALYZE : EXPLAIN) + select_sql).map(QUERY_PLAN).join(CRLF) 1273: end
Run a full text search on PostgreSQL. By default, searching for the inclusion of any of the terms in any of the cols.
Options:
:language : | The language to use for the search (default: ‘simple’) |
:plain : | Whether a plain search should be used (default: false). In this case, terms should be a single string, and it will do a search where cols contains all of the words in terms. This ignores search operators in terms. |
:phrase : | Similar to :plain, but also adding an ILIKE filter to ensure that returned rows also include the exact phrase used. |
:rank : | Set to true to order by the rank, so that closer matches are returned first. |
# File lib/sequel/adapters/shared/postgres.rb, line 1291 1291: def full_text_search(cols, terms, opts = OPTS) 1292: lang = Sequel.cast(opts[:language] || 'simple', :regconfig) 1293: terms = terms.join(' | ') if terms.is_a?(Array) 1294: columns = full_text_string_join(cols) 1295: query_func = (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery 1296: vector = Sequel.function(:to_tsvector, lang, columns) 1297: query = Sequel.function(query_func, lang, terms) 1298: 1299: ds = where(Sequel.lit(["(", " @@ ", ")"], vector, query)) 1300: 1301: if opts[:phrase] 1302: ds = ds.grep(cols, "%#{escape_like(terms)}%", :case_insensitive=>true) 1303: end 1304: 1305: if opts[:rank] 1306: ds = ds.order{ts_rank_cd(vector, query)} 1307: end 1308: 1309: ds 1310: end
Insert given values into the database.
# File lib/sequel/adapters/shared/postgres.rb, line 1313 1313: def insert(*values) 1314: if @opts[:returning] 1315: # Already know which columns to return, let the standard code handle it 1316: super 1317: elsif @opts[:sql] || @opts[:disable_insert_returning] 1318: # Raw SQL used or RETURNING disabled, just use the default behavior 1319: # and return nil since sequence is not known. 1320: super 1321: nil 1322: else 1323: # Force the use of RETURNING with the primary key value, 1324: # unless it has been disabled. 1325: returning(insert_pk).insert(*values){|r| return r.values.first} 1326: end 1327: end
Insert a record returning the record inserted. Always returns nil without inserting a query if disable_insert_returning is used.
# File lib/sequel/adapters/shared/postgres.rb, line 1331 1331: def insert_select(*values) 1332: return unless supports_insert_select? 1333: server?(:default).with_sql_first(insert_select_sql(*values)) 1334: end
The SQL to use for an insert_select, adds a RETURNING clause to the insert unless the RETURNING clause is already present.
# File lib/sequel/adapters/shared/postgres.rb, line 1338 1338: def insert_select_sql(*values) 1339: ds = opts[:returning] ? self : returning 1340: ds.insert_sql(*values) 1341: 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 1348 1348: def lock(mode, opts=OPTS) 1349: if block_given? # perform locking inside a transaction and yield to block 1350: @db.transaction(opts){lock(mode, opts); yield} 1351: else 1352: sql = 'LOCK TABLE ' 1353: source_list_append(sql, @opts[:from]) 1354: mode = mode.to_s.upcase.strip 1355: unless LOCK_MODES.include?(mode) 1356: raise Error, "Unsupported lock mode: #{mode}" 1357: end 1358: sql << " IN #{mode} MODE" 1359: @db.execute(sql, opts) 1360: end 1361: nil 1362: end
# File lib/sequel/adapters/shared/postgres.rb, line 1364 1364: def supports_cte?(type=:select) 1365: if type == :select 1366: server_version >= 80400 1367: else 1368: server_version >= 90100 1369: end 1370: 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 1374 1374: def supports_cte_in_subqueries? 1375: supports_cte? 1376: end
DISTINCT ON is a PostgreSQL extension
# File lib/sequel/adapters/shared/postgres.rb, line 1379 1379: def supports_distinct_on? 1380: true 1381: end
PostgreSQL 9.3rc1+ supports lateral subqueries
# File lib/sequel/adapters/shared/postgres.rb, line 1389 1389: def supports_lateral_subqueries? 1390: server_version >= 90300 1391: end
PostgreSQL supports modifying joined datasets
# File lib/sequel/adapters/shared/postgres.rb, line 1394 1394: def supports_modifying_joins? 1395: true 1396: end
PostgreSQL supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/postgres.rb, line 1404 1404: def supports_regexp? 1405: true 1406: end
Returning is always supported.
# File lib/sequel/adapters/shared/postgres.rb, line 1399 1399: def supports_returning?(type) 1400: true 1401: end
PostgreSQL supports timezones in literal timestamps
# File lib/sequel/adapters/shared/postgres.rb, line 1409 1409: def supports_timestamp_timezones? 1410: true 1411: 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 1433 1433: def truncate(opts = OPTS) 1434: if opts.empty? 1435: super() 1436: else 1437: clone(:truncate_opts=>opts).truncate 1438: end 1439: 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 1452 1452: def _import(columns, values, opts=OPTS) 1453: if @opts[:returning] 1454: statements = multi_insert_sql(columns, values) 1455: @db.transaction(opts.merge(:server=>@opts[:server])) do 1456: statements.map{|st| returning_fetch_rows(st)} 1457: end.first.map{|v| v.length == 1 ? v.values.first : v} 1458: elsif opts[:return] == :primary_key 1459: returning(insert_pk)._import(columns, values, opts) 1460: else 1461: super 1462: end 1463: end