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.
-
#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
PostgreSQL specific full text search syntax, using tsearch2 (included in 8.3 by default, and available for earlier versions as an add-on).
-
#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_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
1136 1137 1138 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1136 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.
1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1143 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 |
#explain(opts = OPTS) ⇒ Object
Return the results of an EXPLAIN query as a string
1167 1168 1169 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1167 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.
1172 1173 1174 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1172 def for_share lock_style(:share) end |
#full_text_search(cols, terms, opts = OPTS) ⇒ Object
PostgreSQL specific full text search syntax, using tsearch2 (included in 8.3 by default, and available for earlier versions as an add-on).
1178 1179 1180 1181 1182 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1178 def full_text_search(cols, terms, opts = OPTS) lang = opts[:language] || 'simple' terms = terms.join(' | ') if terms.is_a?(Array) filter("to_tsvector(?::regconfig, ?) @@ to_tsquery(?::regconfig, ?)", lang, full_text_string_join(cols), lang, terms) end |
#insert(*values) ⇒ Object
Insert given values into the database.
1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1185 def insert(*values) if @opts[:returning] # already know which columns to return, let the standard code # handle it super elsif @opts[:sql] # raw SQL used, so don't know which table is being inserted # into, and therefore can't determine primary key. Run the # insert statement and return nil. super nil else # Force the use of RETURNING with the primary key value. returning(insert_pk).insert(*values){|r| return r.values.first} end end |
#insert_select(*values) ⇒ Object
Insert a record returning the record inserted
1203 1204 1205 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1203 def insert_select(*values) returning.insert(*values){|r| return r} 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.
1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1212 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.
1229 1230 1231 1232 1233 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1229 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+).
1237 1238 1239 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1237 def supports_cte_in_subqueries? supports_cte? end |
#supports_distinct_on? ⇒ Boolean
DISTINCT ON is a PostgreSQL extension
1242 1243 1244 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1242 def supports_distinct_on? true end |
#supports_lateral_subqueries? ⇒ Boolean
PostgreSQL 9.3rc1+ supports lateral subqueries
1247 1248 1249 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1247 def supports_lateral_subqueries? server_version >= 90300 end |
#supports_modifying_joins? ⇒ Boolean
PostgreSQL supports modifying joined datasets
1252 1253 1254 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1252 def true end |
#supports_regexp? ⇒ Boolean
PostgreSQL supports pattern matching via regular expressions
1262 1263 1264 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1262 def supports_regexp? true end |
#supports_returning?(type) ⇒ Boolean
Returning is always supported.
1257 1258 1259 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1257 def supports_returning?(type) true end |
#supports_timestamp_timezones? ⇒ Boolean
PostgreSQL supports timezones in literal timestamps
1267 1268 1269 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1267 def true end |
#supports_window_functions? ⇒ Boolean
PostgreSQL 8.4+ supports window functions
1272 1273 1274 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1272 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
1291 1292 1293 1294 1295 1296 1297 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1291 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.
1300 1301 1302 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1300 def window(name, opts) clone(:window=>(@opts[:window]||[]) + [[name, SQL::Window.new(opts)]]) end |