Module: Sequel::Postgres::DatasetMethods
- Included in:
- JDBC::Postgres::Dataset, Dataset
- Defined in:
- lib/sequel/adapters/shared/postgres.rb
Overview
Instance methods for datasets that connect to a PostgreSQL database.
Defined Under Namespace
Modules: PreparedStatementMethods
Constant Summary collapse
- 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)
Instance Method Summary collapse
-
#analyze ⇒ Object
Return the results of an EXPLAIN ANALYZE query as a string.
-
#complex_expression_sql_append(sql, op, args) ⇒ Object
Handle converting the ruby xor operator (^) into the PostgreSQL xor operator (#), and use the ILIKE and NOT ILIKE operators.
-
#disable_insert_returning ⇒ Object
Disables automatic use of INSERT …
-
#explain(opts = OPTS) ⇒ Object
Return the results of an EXPLAIN query as a string.
-
#for_share ⇒ Object
Return a cloned dataset which will use FOR SHARE to lock returned rows.
-
#full_text_search(cols, terms, opts = OPTS) ⇒ Object
Run a full text search on PostgreSQL.
-
#insert(*values) ⇒ Object
Insert given values into the database.
-
#insert_conflict(opts = OPTS) ⇒ Object
Handle uniqueness violations when inserting, by updating the conflicting row, using ON CONFLICT.
-
#insert_ignore ⇒ Object
Ignore uniqueness/exclusion violations when inserting, using ON CONFLICT DO NOTHING.
-
#insert_select(*values) ⇒ Object
Insert a record returning the record inserted.
-
#insert_select_sql(*values) ⇒ Object
The SQL to use for an insert_select, adds a RETURNING clause to the insert unless the RETURNING clause is already present.
-
#lock(mode, opts = OPTS) ⇒ Object
Locks all tables in the dataset’s FROM clause (but not in JOINs) with the specified mode (e.g. ‘EXCLUSIVE’).
- #supports_cte?(type = :select) ⇒ Boolean
-
#supports_cte_in_subqueries? ⇒ Boolean
PostgreSQL supports using the WITH clause in subqueries if it supports using WITH at all (i.e. on PostgreSQL 8.4+).
-
#supports_distinct_on? ⇒ Boolean
DISTINCT ON is a PostgreSQL extension.
-
#supports_group_cube? ⇒ Boolean
PostgreSQL 9.5+ supports GROUP CUBE.
-
#supports_group_rollup? ⇒ Boolean
PostgreSQL 9.5+ supports GROUP ROLLUP.
-
#supports_grouping_sets? ⇒ Boolean
PostgreSQL 9.5+ supports GROUPING SETS.
-
#supports_insert_conflict? ⇒ Boolean
PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.
-
#supports_insert_select? ⇒ Boolean
True unless insert returning has been disabled for this dataset.
-
#supports_lateral_subqueries? ⇒ Boolean
PostgreSQL 9.3rc1+ supports lateral subqueries.
-
#supports_modifying_joins? ⇒ Boolean
PostgreSQL supports modifying joined datasets.
-
#supports_regexp? ⇒ Boolean
PostgreSQL supports pattern matching via regular expressions.
-
#supports_returning?(type) ⇒ Boolean
Returning is always supported.
-
#supports_timestamp_timezones? ⇒ Boolean
PostgreSQL supports timezones in literal timestamps.
-
#supports_window_functions? ⇒ Boolean
PostgreSQL 8.4+ supports window functions.
-
#truncate(opts = OPTS) ⇒ Object
Truncates the dataset.
-
#window(name, opts) ⇒ Object
Return a clone of the dataset with an addition named window that can be referenced in window functions.
Instance Method Details
#analyze ⇒ Object
Return the results of an EXPLAIN ANALYZE query as a string
1246 1247 1248 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1246 def analyze explain(:analyze=>true) end |
#complex_expression_sql_append(sql, op, args) ⇒ Object
Handle converting the ruby xor operator (^) into the PostgreSQL xor operator (#), and use the ILIKE and NOT ILIKE operators.
1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1253 def complex_expression_sql_append(sql, op, args) case op when :^ j = XOR_OP c = false args.each do |a| sql << j if c literal_append(sql, a) c ||= true end when :ILIKE, :'NOT ILIKE' sql << PAREN_OPEN literal_append(sql, args.at(0)) sql << SPACE << op.to_s << SPACE literal_append(sql, args.at(1)) sql << ESCAPE literal_append(sql, BACKSLASH) sql << PAREN_CLOSE else super end end |
#disable_insert_returning ⇒ Object
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).
1290 1291 1292 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1290 def disable_insert_returning clone(:disable_insert_returning=>true) end |
#explain(opts = OPTS) ⇒ Object
Return the results of an EXPLAIN query as a string
1295 1296 1297 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1295 def explain(opts=OPTS) with_sql((opts[:analyze] ? EXPLAIN_ANALYZE : EXPLAIN) + select_sql).map(QUERY_PLAN).join(CRLF) end |
#for_share ⇒ Object
Return a cloned dataset which will use FOR SHARE to lock returned rows.
1300 1301 1302 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1300 def for_share lock_style(:share) end |
#full_text_search(cols, terms, opts = OPTS) ⇒ Object
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.
- :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.
1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1319 def full_text_search(cols, terms, opts = OPTS) lang = Sequel.cast(opts[:language] || 'simple', :regconfig) unless opts[:tsvector] phrase_cols = full_text_string_join(cols) cols = Sequel.function(:to_tsvector, lang, phrase_cols) end unless opts[:tsquery] phrase_terms = terms.is_a?(Array) ? terms.join(' | ') : terms query_func = (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery terms = Sequel.function(query_func, lang, phrase_terms) end ds = where(Sequel.lit(["(", " @@ ", ")"], cols, terms)) if opts[:phrase] raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery] ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true) end if opts[:rank] ds = ds.reverse{ts_rank_cd(cols, terms)} end ds end |
#insert(*values) ⇒ Object
Insert given values into the database.
1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1348 def insert(*values) if @opts[:returning] # Already know which columns to return, let the standard code handle it super elsif @opts[:sql] || @opts[:disable_insert_returning] # Raw SQL used or RETURNING disabled, just use the default behavior # and return nil since sequence is not known. super nil else # Force the use of RETURNING with the primary key value, # unless it has been disabled. returning(insert_pk).insert(*values){|r| return r.values.first} end end |
#insert_conflict(opts = OPTS) ⇒ Object
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)
1394 1395 1396 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1394 def insert_conflict(opts=OPTS) clone(:insert_conflict => opts) end |
#insert_ignore ⇒ Object
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
1404 1405 1406 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1404 def insert_ignore insert_conflict end |
#insert_select(*values) ⇒ Object
Insert a record returning the record inserted. Always returns nil without inserting a query if disable_insert_returning is used.
1410 1411 1412 1413 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1410 def insert_select(*values) return unless supports_insert_select? server?(:default).with_sql_first(insert_select_sql(*values)) end |
#insert_select_sql(*values) ⇒ Object
The SQL to use for an insert_select, adds a RETURNING clause to the insert unless the RETURNING clause is already present.
1417 1418 1419 1420 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1417 def insert_select_sql(*values) ds = opts[:returning] ? self : returning ds.insert_sql(*values) end |
#lock(mode, opts = OPTS) ⇒ Object
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.
1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1427 def lock(mode, opts=OPTS) if block_given? # perform locking inside a transaction and yield to block @db.transaction(opts){lock(mode, opts); yield} else sql = 'LOCK TABLE '.dup source_list_append(sql, @opts[:from]) mode = mode.to_s.upcase.strip unless LOCK_MODES.include?(mode) raise Error, "Unsupported lock mode: #{mode}" end sql << " IN #{mode} MODE" @db.execute(sql, opts) end nil end |
#supports_cte?(type = :select) ⇒ Boolean
1443 1444 1445 1446 1447 1448 1449 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1443 def supports_cte?(type=:select) if type == :select server_version >= 80400 else server_version >= 90100 end end |
#supports_cte_in_subqueries? ⇒ Boolean
PostgreSQL supports using the WITH clause in subqueries if it supports using WITH at all (i.e. on PostgreSQL 8.4+).
1453 1454 1455 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1453 def supports_cte_in_subqueries? supports_cte? end |
#supports_distinct_on? ⇒ Boolean
DISTINCT ON is a PostgreSQL extension
1458 1459 1460 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1458 def supports_distinct_on? true end |
#supports_group_cube? ⇒ Boolean
PostgreSQL 9.5+ supports GROUP CUBE
1463 1464 1465 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1463 def supports_group_cube? server_version >= 90500 end |
#supports_group_rollup? ⇒ Boolean
PostgreSQL 9.5+ supports GROUP ROLLUP
1468 1469 1470 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1468 def supports_group_rollup? server_version >= 90500 end |
#supports_grouping_sets? ⇒ Boolean
PostgreSQL 9.5+ supports GROUPING SETS
1473 1474 1475 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1473 def supports_grouping_sets? server_version >= 90500 end |
#supports_insert_conflict? ⇒ Boolean
PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.
1483 1484 1485 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1483 def supports_insert_conflict? server_version >= 90500 end |
#supports_insert_select? ⇒ Boolean
True unless insert returning has been disabled for this dataset.
1478 1479 1480 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1478 def supports_insert_select? !@opts[:disable_insert_returning] end |
#supports_lateral_subqueries? ⇒ Boolean
PostgreSQL 9.3rc1+ supports lateral subqueries
1488 1489 1490 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1488 def supports_lateral_subqueries? server_version >= 90300 end |
#supports_modifying_joins? ⇒ Boolean
PostgreSQL supports modifying joined datasets
1493 1494 1495 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1493 def true end |
#supports_regexp? ⇒ Boolean
PostgreSQL supports pattern matching via regular expressions
1503 1504 1505 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1503 def supports_regexp? true end |
#supports_returning?(type) ⇒ Boolean
Returning is always supported.
1498 1499 1500 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1498 def supports_returning?(type) true end |
#supports_timestamp_timezones? ⇒ Boolean
PostgreSQL supports timezones in literal timestamps
1508 1509 1510 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1508 def true end |
#supports_window_functions? ⇒ Boolean
PostgreSQL 8.4+ supports window functions
1513 1514 1515 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1513 def supports_window_functions? server_version >= 80400 end |
#truncate(opts = OPTS) ⇒ Object
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
1532 1533 1534 1535 1536 1537 1538 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1532 def truncate(opts = OPTS) if opts.empty? super() else clone(:truncate_opts=>opts).truncate end end |
#window(name, opts) ⇒ Object
Return a clone of the dataset with an addition named window that can be referenced in window functions.
1541 1542 1543 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1541 def window(name, opts) clone(:window=>(@opts[:window]||[]) + [[name, SQL::Window.new(opts)]]) end |