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
- DELETE_CLAUSE_METHODS =
Dataset.clause_methods(:delete, %w'delete from using where returning')
- DELETE_CLAUSE_METHODS_91 =
Dataset.clause_methods(:delete, %w'with delete from using where returning')
- EXCLUSIVE =
'EXCLUSIVE'.freeze
- EXPLAIN =
'EXPLAIN '.freeze
- EXPLAIN_ANALYZE =
'EXPLAIN ANALYZE '.freeze
- FOR_SHARE =
' FOR SHARE'.freeze
- INSERT_CLAUSE_METHODS =
Dataset.clause_methods(:insert, %w'insert into columns values returning')
- INSERT_CLAUSE_METHODS_91 =
Dataset.clause_methods(:insert, %w'with insert into columns values returning')
- 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
- SELECT_CLAUSE_METHODS =
Dataset.clause_methods(:select, %w'select distinct columns from join where group having compounds order limit lock')
- SELECT_CLAUSE_METHODS_84 =
Dataset.clause_methods(:select, %w'with select distinct columns from join where group having window compounds order limit lock')
- SHARE =
'SHARE'.freeze
- SHARE_ROW_EXCLUSIVE =
'SHARE ROW EXCLUSIVE'.freeze
- SHARE_UPDATE_EXCLUSIVE =
'SHARE UPDATE EXCLUSIVE'.freeze
- SQL_WITH_RECURSIVE =
"WITH RECURSIVE ".freeze
- UPDATE_CLAUSE_METHODS =
Dataset.clause_methods(:update, %w'update table set from where returning')
- UPDATE_CLAUSE_METHODS_91 =
Dataset.clause_methods(:update, %w'with update table set from where returning')
- 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
- 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.
-
#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’).
-
#multi_insert_sql(columns, values) ⇒ Object
PostgreSQL allows inserting multiple rows at once.
-
#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
1156 1157 1158 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1156 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.
1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1163 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).
1200 1201 1202 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1200 def disable_insert_returning clone(:disable_insert_returning=>true) end |
#explain(opts = OPTS) ⇒ Object
Return the results of an EXPLAIN query as a string
1205 1206 1207 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1205 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.
1210 1211 1212 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1210 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.
1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1224 def full_text_search(cols, terms, opts = OPTS) lang = opts[:language] || 'simple' terms = terms.join(' | ') if terms.is_a?(Array) to_tsquery = (opts[:phrase] || opts[:plain]) ? 'plainto_tsquery' : 'to_tsquery' ds = where(Sequel.lit(["(to_tsvector(", "::regconfig, ", ") @@ #{to_tsquery}(", "::regconfig, ", "))"], lang, full_text_string_join(cols), lang, terms)) if opts[:phrase] ds = ds.grep(cols, "%#{escape_like(terms)}%", :case_insensitive=>true) end ds end |
#insert(*values) ⇒ Object
Insert given values into the database.
1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1239 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.
1257 1258 1259 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1257 def insert_select(*values) returning.insert(*values){|r| return r} unless @opts[:disable_insert_returning] 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.
1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1266 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 |
#multi_insert_sql(columns, values) ⇒ Object
PostgreSQL allows inserting multiple rows at once.
1283 1284 1285 1286 1287 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1283 def multi_insert_sql(columns, values) sql = LiteralString.new('VALUES ') expression_list_append(sql, values.map{|r| Array(r)}) [insert_sql(columns, sql)] 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+).
1291 1292 1293 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1291 def supports_cte_in_subqueries? supports_cte? end |
#supports_distinct_on? ⇒ Boolean
DISTINCT ON is a PostgreSQL extension
1296 1297 1298 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1296 def supports_distinct_on? true end |
#supports_insert_select? ⇒ Boolean
True unless insert returning has been disabled for this dataset.
1301 1302 1303 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1301 def supports_insert_select? !@opts[:disable_insert_returning] end |
#supports_lateral_subqueries? ⇒ Boolean
PostgreSQL 9.3rc1+ supports lateral subqueries
1306 1307 1308 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1306 def supports_lateral_subqueries? server_version >= 90300 end |
#supports_modifying_joins? ⇒ Boolean
PostgreSQL supports modifying joined datasets
1311 1312 1313 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1311 def true end |
#supports_regexp? ⇒ Boolean
PostgreSQL supports pattern matching via regular expressions
1321 1322 1323 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1321 def supports_regexp? true end |
#supports_returning?(type) ⇒ Boolean
Returning is always supported.
1316 1317 1318 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1316 def supports_returning?(type) true end |
#supports_timestamp_timezones? ⇒ Boolean
PostgreSQL supports timezones in literal timestamps
1326 1327 1328 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1326 def true end |
#supports_window_functions? ⇒ Boolean
PostgreSQL 8.4+ supports window functions
1331 1332 1333 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1331 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
1350 1351 1352 1353 1354 1355 1356 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1350 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.
1359 1360 1361 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1359 def window(name, opts) clone(:window=>(@opts[:window]||[]) + [[name, SQL::Window.new(opts)]]) end |