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_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_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
1227 1228 1229 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1227 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.
1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1234 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).
1271 1272 1273 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1271 def disable_insert_returning clone(:disable_insert_returning=>true) end |
#explain(opts = OPTS) ⇒ Object
Return the results of an EXPLAIN query as a string
1276 1277 1278 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1276 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.
1281 1282 1283 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1281 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.
1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1300 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.order{ts_rank_cd(cols, terms)} end ds end |
#insert(*values) ⇒ Object
Insert given values into the database.
1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1329 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_select(*values) ⇒ Object
Insert a record returning the record inserted. Always returns nil without inserting a query if disable_insert_returning is used.
1347 1348 1349 1350 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1347 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.
1354 1355 1356 1357 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1354 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.
1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1364 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 ' 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
1380 1381 1382 1383 1384 1385 1386 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1380 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+).
1390 1391 1392 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1390 def supports_cte_in_subqueries? supports_cte? end |
#supports_distinct_on? ⇒ Boolean
DISTINCT ON is a PostgreSQL extension
1395 1396 1397 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1395 def supports_distinct_on? true end |
#supports_insert_select? ⇒ Boolean
True unless insert returning has been disabled for this dataset.
1400 1401 1402 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1400 def supports_insert_select? !@opts[:disable_insert_returning] end |
#supports_lateral_subqueries? ⇒ Boolean
PostgreSQL 9.3rc1+ supports lateral subqueries
1405 1406 1407 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1405 def supports_lateral_subqueries? server_version >= 90300 end |
#supports_modifying_joins? ⇒ Boolean
PostgreSQL supports modifying joined datasets
1410 1411 1412 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1410 def true end |
#supports_regexp? ⇒ Boolean
PostgreSQL supports pattern matching via regular expressions
1420 1421 1422 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1420 def supports_regexp? true end |
#supports_returning?(type) ⇒ Boolean
Returning is always supported.
1415 1416 1417 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1415 def supports_returning?(type) true end |
#supports_timestamp_timezones? ⇒ Boolean
PostgreSQL supports timezones in literal timestamps
1425 1426 1427 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1425 def true end |
#supports_window_functions? ⇒ Boolean
PostgreSQL 8.4+ supports window functions
1430 1431 1432 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1430 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
1449 1450 1451 1452 1453 1454 1455 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1449 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.
1458 1459 1460 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1458 def window(name, opts) clone(:window=>(@opts[:window]||[]) + [[name, SQL::Window.new(opts)]]) end |