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')
- LOCK =
'LOCK TABLE %s IN %s MODE'.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
- 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
- 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
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 (#).
-
#explain(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 = {}) ⇒ 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 = {}) ⇒ 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_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 = {}) ⇒ 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
1016 1017 1018 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1016 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 (#).
1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1022 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 else super end end |
#explain(opts = {}) ⇒ Object
Return the results of an EXPLAIN query as a string
1038 1039 1040 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1038 def explain(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.
1043 1044 1045 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1043 def for_share lock_style(:share) end |
#full_text_search(cols, terms, 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).
1049 1050 1051 1052 1053 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1049 def full_text_search(cols, terms, 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.
1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1056 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
1074 1075 1076 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1074 def insert_select(*values) returning.insert(*values){|r| return r} end |
#lock(mode, 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.
1083 1084 1085 1086 1087 1088 1089 1090 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1083 def lock(mode, opts={}) if block_given? # perform locking inside a transaction and yield to block @db.transaction(opts){lock(mode, opts); yield} else @db.execute(LOCK % [source_list(@opts[:from]), mode], opts) # lock without a transaction end nil end |
#multi_insert_sql(columns, values) ⇒ Object
PostgreSQL allows inserting multiple rows at once.
1093 1094 1095 1096 1097 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1093 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+).
1101 1102 1103 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1101 def supports_cte_in_subqueries? supports_cte? end |
#supports_distinct_on? ⇒ Boolean
DISTINCT ON is a PostgreSQL extension
1106 1107 1108 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1106 def supports_distinct_on? true end |
#supports_modifying_joins? ⇒ Boolean
PostgreSQL supports modifying joined datasets
1111 1112 1113 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1111 def true end |
#supports_regexp? ⇒ Boolean
PostgreSQL supports pattern matching via regular expressions
1121 1122 1123 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1121 def supports_regexp? true end |
#supports_returning?(type) ⇒ Boolean
Returning is always supported.
1116 1117 1118 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1116 def supports_returning?(type) true end |
#supports_timestamp_timezones? ⇒ Boolean
PostgreSQL supports timezones in literal timestamps
1126 1127 1128 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1126 def true end |
#supports_window_functions? ⇒ Boolean
PostgreSQL 8.4+ supports window functions
1131 1132 1133 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1131 def supports_window_functions? server_version >= 80400 end |
#truncate(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
1150 1151 1152 1153 1154 1155 1156 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1150 def truncate(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.
1159 1160 1161 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 1159 def window(name, opts) clone(:window=>(@opts[:window]||[]) + [[name, SQL::Window.new(opts)]]) end |