Module Sequel::Postgres::DatasetMethods
In: lib/sequel/adapters/shared/postgres.rb

Instance methods for datasets that connect to a PostgreSQL database.

Methods

_import   analyze   complex_expression_sql_append   explain   for_share   full_text_search   insert   insert_select   lock   multi_insert_sql   supports_cte_in_subqueries?   supports_distinct_on?   supports_modifying_joins?   supports_regexp?   supports_returning?   supports_timestamp_timezones?   supports_window_functions?   truncate   window  

Classes and Modules

Module Sequel::Postgres::DatasetMethods::PreparedStatementMethods

Constants

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}

Public Instance methods

analyze()

Return the results of an EXPLAIN ANALYZE query as a string

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1109
1109:       def analyze
1110:         explain(:analyze=>true)
1111:       end
complex_expression_sql_append(sql, op, args)

Handle converting the ruby xor operator (^) into the PostgreSQL xor operator (#), and use the ILIKE and NOT ILIKE operators.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1116
1116:       def complex_expression_sql_append(sql, op, args)
1117:         case op
1118:         when :^
1119:           j = XOR_OP
1120:           c = false
1121:           args.each do |a|
1122:             sql << j if c
1123:             literal_append(sql, a)
1124:             c ||= true
1125:           end
1126:         when :ILIKE, 'NOT ILIKE''NOT ILIKE'
1127:           sql << PAREN_OPEN
1128:           literal_append(sql, args.at(0))
1129:           sql << SPACE << op.to_s << SPACE
1130:           literal_append(sql, args.at(1))
1131:           sql << ESCAPE
1132:           literal_append(sql, BACKSLASH)
1133:           sql << PAREN_CLOSE
1134:         else
1135:           super
1136:         end
1137:       end
explain(opts={})

Return the results of an EXPLAIN query as a string

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1140
1140:       def explain(opts={})
1141:         with_sql((opts[:analyze] ? EXPLAIN_ANALYZE : EXPLAIN) + select_sql).map(QUERY_PLAN).join(CRLF)
1142:       end
for_share()

Return a cloned dataset which will use FOR SHARE to lock returned rows.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1145
1145:       def for_share
1146:         lock_style(:share)
1147:       end
full_text_search(cols, terms, opts = {})

PostgreSQL specific full text search syntax, using tsearch2 (included in 8.3 by default, and available for earlier versions as an add-on).

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1151
1151:       def full_text_search(cols, terms, opts = {})
1152:         lang = opts[:language] || 'simple'
1153:         terms = terms.join(' | ') if terms.is_a?(Array)
1154:         filter("to_tsvector(?::regconfig, ?) @@ to_tsquery(?::regconfig, ?)", lang, full_text_string_join(cols), lang, terms)
1155:       end
insert(*values)

Insert given values into the database.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1158
1158:       def insert(*values)
1159:         if @opts[:returning]
1160:           # already know which columns to return, let the standard code
1161:           # handle it
1162:           super
1163:         elsif @opts[:sql]
1164:           # raw SQL used, so don't know which table is being inserted
1165:           # into, and therefore can't determine primary key.  Run the
1166:           # insert statement and return nil.
1167:           super
1168:           nil
1169:         else
1170:           # Force the use of RETURNING with the primary key value.
1171:           returning(insert_pk).insert(*values){|r| return r.values.first}
1172:         end
1173:       end
insert_select(*values)

Insert a record returning the record inserted

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1176
1176:       def insert_select(*values)
1177:         returning.insert(*values){|r| return r}
1178:       end
lock(mode, opts={}) {|| ...}

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.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1185
1185:       def lock(mode, opts={})
1186:         if block_given? # perform locking inside a transaction and yield to block
1187:           @db.transaction(opts){lock(mode, opts); yield}
1188:         else
1189:           sql = 'LOCK TABLE '
1190:           source_list_append(sql, @opts[:from])
1191:           mode = mode.to_s.upcase.strip
1192:           unless LOCK_MODES.include?(mode)
1193:             Sequel::Deprecation.deprecate("Calling Dataset#lock with an unsupported lock mode will raise an Error in Sequel 4.")
1194:           end
1195:           sql << " IN #{mode} MODE"
1196:           @db.execute(sql, opts)
1197:         end
1198:         nil
1199:       end
multi_insert_sql(columns, values)

PostgreSQL allows inserting multiple rows at once.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1202
1202:       def multi_insert_sql(columns, values)
1203:         sql = LiteralString.new('VALUES ')
1204:         expression_list_append(sql, values.map{|r| Array(r)})
1205:         [insert_sql(columns, sql)]
1206:       end
supports_cte_in_subqueries?()

PostgreSQL supports using the WITH clause in subqueries if it supports using WITH at all (i.e. on PostgreSQL 8.4+).

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1210
1210:       def supports_cte_in_subqueries?
1211:         supports_cte?
1212:       end
supports_distinct_on?()

DISTINCT ON is a PostgreSQL extension

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1215
1215:       def supports_distinct_on?
1216:         true
1217:       end
supports_modifying_joins?()

PostgreSQL supports modifying joined datasets

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1220
1220:       def supports_modifying_joins?
1221:         true
1222:       end
supports_regexp?()

PostgreSQL supports pattern matching via regular expressions

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1230
1230:       def supports_regexp?
1231:         true
1232:       end
supports_returning?(type)

Returning is always supported.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1225
1225:       def supports_returning?(type)
1226:         true
1227:       end
supports_timestamp_timezones?()

PostgreSQL supports timezones in literal timestamps

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1235
1235:       def supports_timestamp_timezones?
1236:         true
1237:       end
supports_window_functions?()

PostgreSQL 8.4+ supports window functions

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1240
1240:       def supports_window_functions?
1241:         server_version >= 80400
1242:       end
truncate(opts = {})

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

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1259
1259:       def truncate(opts = {})
1260:         if opts.empty?
1261:           super()
1262:         else
1263:           clone(:truncate_opts=>opts).truncate
1264:         end
1265:       end
window(name, opts)

Return a clone of the dataset with an addition named window that can be referenced in window functions.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1268
1268:       def window(name, opts)
1269:         clone(:window=>(@opts[:window]||[]) + [[name, SQL::Window.new(opts)]])
1270:       end

Protected Instance methods

_import(columns, values, opts={})

If returned primary keys are requested, use RETURNING unless already set on the dataset. If RETURNING is already set, use existing returning values. If RETURNING is only set to return a single columns, return an array of just that column. Otherwise, return an array of hashes.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1278
1278:       def _import(columns, values, opts={})
1279:         if @opts[:returning]
1280:           statements = multi_insert_sql(columns, values)
1281:           @db.transaction(opts.merge(:server=>@opts[:server])) do
1282:             statements.map{|st| returning_fetch_rows(st)}
1283:           end.first.map{|v| v.length == 1 ? v.values.first : v}
1284:         elsif opts[:return] == :primary_key
1285:           returning(insert_pk)._import(columns, values, opts)
1286:         else
1287:           super
1288:         end
1289:       end

[Validate]