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{|s| s.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
1203 1204 1205 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1203 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.
1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1210 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).
1247 1248 1249 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1247 def disable_insert_returning clone(:disable_insert_returning=>true) end |
#explain(opts = OPTS) ⇒ Object
Return the results of an EXPLAIN query as a string
1252 1253 1254 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1252 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.
1257 1258 1259 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1257 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.
1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1272 def full_text_search(cols, terms, opts = OPTS) lang = Sequel.cast(opts[:language] || 'simple', :regconfig) terms = terms.join(' | ') if terms.is_a?(Array) columns = full_text_string_join(cols) query_func = (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery vector = Sequel.function(:to_tsvector, lang, columns) query = Sequel.function(query_func, lang, terms) ds = where(Sequel.lit(["(", " @@ ", ")"], vector, query)) if opts[:phrase] ds = ds.grep(cols, "%#{escape_like(terms)}%", :case_insensitive=>true) end if opts[:rank] ds = ds.order{ts_rank_cd(vector, query)} end ds end |
#insert(*values) ⇒ Object
Insert given values into the database.
1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1294 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.
1312 1313 1314 1315 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1312 def insert_select(*values) return unless supports_insert_select? 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.
1319 1320 1321 1322 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1319 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.
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 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
1345 1346 1347 1348 1349 1350 1351 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1345 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+).
1355 1356 1357 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1355 def supports_cte_in_subqueries? supports_cte? end |
#supports_distinct_on? ⇒ Boolean
DISTINCT ON is a PostgreSQL extension
1360 1361 1362 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1360 def supports_distinct_on? true end |
#supports_insert_select? ⇒ Boolean
True unless insert returning has been disabled for this dataset.
1365 1366 1367 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1365 def supports_insert_select? !@opts[:disable_insert_returning] end |
#supports_lateral_subqueries? ⇒ Boolean
PostgreSQL 9.3rc1+ supports lateral subqueries
1370 1371 1372 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1370 def supports_lateral_subqueries? server_version >= 90300 end |
#supports_modifying_joins? ⇒ Boolean
PostgreSQL supports modifying joined datasets
1375 1376 1377 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1375 def true end |
#supports_regexp? ⇒ Boolean
PostgreSQL supports pattern matching via regular expressions
1385 1386 1387 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1385 def supports_regexp? true end |
#supports_returning?(type) ⇒ Boolean
Returning is always supported.
1380 1381 1382 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1380 def supports_returning?(type) true end |
#supports_timestamp_timezones? ⇒ Boolean
PostgreSQL supports timezones in literal timestamps
1390 1391 1392 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1390 def true end |
#supports_window_functions? ⇒ Boolean
PostgreSQL 8.4+ supports window functions
1395 1396 1397 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1395 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
1414 1415 1416 1417 1418 1419 1420 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1414 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.
1423 1424 1425 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1423 def window(name, opts) clone(:window=>(@opts[:window]||[]) + [[name, SQL::Window.new(opts)]]) end |