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(&:freeze) |
SKIP_LOCKED | = | " SKIP LOCKED".freeze |
Return the results of an EXPLAIN ANALYZE query as a string
# File lib/sequel/adapters/shared/postgres.rb, line 1275 1275: def analyze 1276: explain(:analyze=>true) 1277: 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 1282 1282: def complex_expression_sql_append(sql, op, args) 1283: case op 1284: when :^ 1285: j = XOR_OP 1286: c = false 1287: args.each do |a| 1288: sql << j if c 1289: literal_append(sql, a) 1290: c ||= true 1291: end 1292: when :ILIKE, 'NOT ILIKE''NOT ILIKE' 1293: sql << PAREN_OPEN 1294: literal_append(sql, args.at(0)) 1295: sql << SPACE << op.to_s << SPACE 1296: literal_append(sql, args.at(1)) 1297: sql << ESCAPE 1298: literal_append(sql, BACKSLASH) 1299: sql << PAREN_CLOSE 1300: else 1301: super 1302: end 1303: 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 1319 1319: def disable_insert_returning 1320: clone(:disable_insert_returning=>true) 1321: end
Return the results of an EXPLAIN query as a string
# File lib/sequel/adapters/shared/postgres.rb, line 1324 1324: def explain(opts=OPTS) 1325: with_sql((opts[:analyze] ? EXPLAIN_ANALYZE : EXPLAIN) + select_sql).map(QUERY_PLAN).join(CRLF) 1326: 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:
:headline : | Append a expression to the selected columns aliased to headline that contains an extract of the matched text. |
: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. |
:to_tsquery : | Can be set to :plain or :phrase to specify the function to use to convert the terms to a ts_query. |
:tsquery : | Specifies the terms argument is already a valid SQL expression returning a tsquery, and can be used directly in the query. |
:tsvector : | Specifies the cols argument is already a valid SQL expression returning a tsvector, and can be used directly in the query. |
# File lib/sequel/adapters/shared/postgres.rb, line 1352 1352: def full_text_search(cols, terms, opts = OPTS) 1353: lang = Sequel.cast(opts[:language] || 'simple', :regconfig) 1354: 1355: unless opts[:tsvector] 1356: phrase_cols = full_text_string_join(cols) 1357: cols = Sequel.function(:to_tsvector, lang, phrase_cols) 1358: end 1359: 1360: unless opts[:tsquery] 1361: phrase_terms = terms.is_a?(Array) ? terms.join(' | ') : terms 1362: 1363: query_func = case to_tsquery = opts[:to_tsquery] 1364: when :phrase, :plain 1365: "#{to_tsquery}to_tsquery""#{to_tsquery}to_tsquery" 1366: else 1367: (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery 1368: end 1369: 1370: terms = Sequel.function(query_func, lang, phrase_terms) 1371: end 1372: 1373: ds = where(Sequel.lit(["(", " @@ ", ")"], cols, terms)) 1374: 1375: if opts[:phrase] 1376: raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery] 1377: ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true) 1378: end 1379: 1380: if opts[:rank] 1381: ds = ds.reverse{ts_rank_cd(cols, terms)} 1382: end 1383: 1384: if opts[:headline] 1385: ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)} 1386: end 1387: 1388: ds 1389: end
Insert given values into the database.
# File lib/sequel/adapters/shared/postgres.rb, line 1392 1392: def insert(*values) 1393: if @opts[:returning] 1394: # Already know which columns to return, let the standard code handle it 1395: super 1396: elsif @opts[:sql] || @opts[:disable_insert_returning] 1397: # Raw SQL used or RETURNING disabled, just use the default behavior 1398: # and return nil since sequence is not known. 1399: super 1400: nil 1401: else 1402: # Force the use of RETURNING with the primary key value, 1403: # unless it has been disabled. 1404: returning(insert_pk).insert(*values){|r| return r.values.first} 1405: end 1406: end
Handle uniqueness violations when inserting, by updating the conflicting row, using ON CONFLICT. With no options, uses ON CONFLICT DO NOTHING. Options:
:constraint : | An explicit constraint name, has precendence over :target. |
:target : | The column name or expression to handle uniqueness violations on. |
:update : | A hash of columns and values to set. Uses ON CONFLICT DO UPDATE. |
:update_where : | A WHERE condition to use for the update. |
Examples:
DB[:table].insert_conflict.insert(:a=>1, :b=>2) # INSERT INTO TABLE (a, b) VALUES (1, 2) # ON CONFLICT DO NOTHING DB[:table].insert_conflict(:constraint=>:table_a_uidx).insert(:a=>1, :b=>2) # INSERT INTO TABLE (a, b) VALUES (1, 2) # ON CONFLICT ON CONSTRAINT table_a_uidx DO NOTHING DB[:table].insert_conflict(:target=>:a).insert(:a=>1, :b=>2) # INSERT INTO TABLE (a, b) VALUES (1, 2) # ON CONFLICT (a) DO NOTHING DB[:table].insert_conflict(:target=>:a, :update=>{:b=>:excluded__b}).insert(:a=>1, :b=>2) # INSERT INTO TABLE (a, b) VALUES (1, 2) # ON CONFLICT (a) DO UPDATE SET b = excluded.b DB[:table].insert_conflict(:constraint=>:table_a_uidx, :update=>{:b=>:excluded__b}, :update_where=>{:table__status_id=>1}).insert(:a=>1, :b=>2) # INSERT INTO TABLE (a, b) VALUES (1, 2) # ON CONFLICT ON CONSTRAINT table_a_uidx # DO UPDATE SET b = excluded.b WHERE (table.status_id = 1)
# File lib/sequel/adapters/shared/postgres.rb, line 1438 1438: def insert_conflict(opts=OPTS) 1439: clone(:insert_conflict => opts) 1440: end
Ignore uniqueness/exclusion violations when inserting, using ON CONFLICT DO NOTHING. Exists mostly for compatibility to MySQL‘s insert_ignore. Example:
DB[:table].insert_ignore.insert(:a=>1, :b=>2) # INSERT INTO TABLE (a, b) VALUES (1, 2) # ON CONFLICT DO NOTHING
# File lib/sequel/adapters/shared/postgres.rb, line 1448 1448: def insert_ignore 1449: insert_conflict 1450: 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 1454 1454: def insert_select(*values) 1455: return unless supports_insert_select? 1456: server?(:default).with_sql_first(insert_select_sql(*values)) 1457: 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 1461 1461: def insert_select_sql(*values) 1462: ds = opts[:returning] ? self : returning 1463: ds.insert_sql(*values) 1464: 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 1471 1471: def lock(mode, opts=OPTS) 1472: if block_given? # perform locking inside a transaction and yield to block 1473: @db.transaction(opts){lock(mode, opts); yield} 1474: else 1475: sql = 'LOCK TABLE '.dup 1476: source_list_append(sql, @opts[:from]) 1477: mode = mode.to_s.upcase.strip 1478: unless LOCK_MODES.include?(mode) 1479: raise Error, "Unsupported lock mode: #{mode}" 1480: end 1481: sql << " IN #{mode} MODE" 1482: @db.execute(sql, opts) 1483: end 1484: nil 1485: end
# File lib/sequel/adapters/shared/postgres.rb, line 1487 1487: def supports_cte?(type=:select) 1488: if type == :select 1489: server_version >= 80400 1490: else 1491: server_version >= 90100 1492: end 1493: 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 1497 1497: def supports_cte_in_subqueries? 1498: supports_cte? 1499: end
DISTINCT ON is a PostgreSQL extension
# File lib/sequel/adapters/shared/postgres.rb, line 1502 1502: def supports_distinct_on? 1503: true 1504: end
PostgreSQL 9.5+ supports GROUP CUBE
# File lib/sequel/adapters/shared/postgres.rb, line 1507 1507: def supports_group_cube? 1508: server_version >= 90500 1509: end
PostgreSQL 9.5+ supports GROUP ROLLUP
# File lib/sequel/adapters/shared/postgres.rb, line 1512 1512: def supports_group_rollup? 1513: server_version >= 90500 1514: end
PostgreSQL 9.5+ supports GROUPING SETS
# File lib/sequel/adapters/shared/postgres.rb, line 1517 1517: def supports_grouping_sets? 1518: server_version >= 90500 1519: end
PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.
# File lib/sequel/adapters/shared/postgres.rb, line 1527 1527: def supports_insert_conflict? 1528: server_version >= 90500 1529: end
PostgreSQL 9.3rc1+ supports lateral subqueries
# File lib/sequel/adapters/shared/postgres.rb, line 1532 1532: def supports_lateral_subqueries? 1533: server_version >= 90300 1534: end
PostgreSQL supports modifying joined datasets
# File lib/sequel/adapters/shared/postgres.rb, line 1537 1537: def supports_modifying_joins? 1538: true 1539: end
PostgreSQL supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/postgres.rb, line 1547 1547: def supports_regexp? 1548: true 1549: end
Returning is always supported.
# File lib/sequel/adapters/shared/postgres.rb, line 1542 1542: def supports_returning?(type) 1543: true 1544: end
PostgreSQL 9.5+ supports SKIP LOCKED.
# File lib/sequel/adapters/shared/postgres.rb, line 1552 1552: def supports_skip_locked? 1553: server_version >= 90500 1554: end
PostgreSQL supports timezones in literal timestamps
# File lib/sequel/adapters/shared/postgres.rb, line 1557 1557: def supports_timestamp_timezones? 1558: true 1559: 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 1581 1581: def truncate(opts = OPTS) 1582: if opts.empty? 1583: super() 1584: else 1585: clone(:truncate_opts=>opts).truncate 1586: end 1587: 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 1600 1600: def _import(columns, values, opts=OPTS) 1601: if @opts[:returning] 1602: statements = multi_insert_sql(columns, values) 1603: @db.transaction(Hash[opts].merge!(:server=>@opts[:server])) do 1604: statements.map{|st| returning_fetch_rows(st)} 1605: end.first.map{|v| v.length == 1 ? v.values.first : v} 1606: elsif opts[:return] == :primary_key 1607: returning(insert_pk)._import(columns, values, opts) 1608: else 1609: super 1610: end 1611: end