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

Instance methods for datasets that connect to a PostgreSQL database.

Methods

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

Return the results of an EXPLAIN ANALYZE query as a string

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1135
1135:       def analyze
1136:         explain(:analyze=>true)
1137:       end

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 1142
1142:       def complex_expression_sql_append(sql, op, args)
1143:         case op
1144:         when :^
1145:           j = XOR_OP
1146:           c = false
1147:           args.each do |a|
1148:             sql << j if c
1149:             literal_append(sql, a)
1150:             c ||= true
1151:           end
1152:         when :ILIKE, 'NOT ILIKE''NOT ILIKE'
1153:           sql << PAREN_OPEN
1154:           literal_append(sql, args.at(0))
1155:           sql << SPACE << op.to_s << SPACE
1156:           literal_append(sql, args.at(1))
1157:           sql << ESCAPE
1158:           literal_append(sql, BACKSLASH)
1159:           sql << PAREN_CLOSE
1160:         else
1161:           super
1162:         end
1163:       end

Return the results of an EXPLAIN query as a string

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1166
1166:       def explain(opts=OPTS)
1167:         with_sql((opts[:analyze] ? EXPLAIN_ANALYZE : EXPLAIN) + select_sql).map(QUERY_PLAN).join(CRLF)
1168:       end

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

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1171
1171:       def for_share
1172:         lock_style(:share)
1173:       end

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 1177
1177:       def full_text_search(cols, terms, opts = OPTS)
1178:         lang = opts[:language] || 'simple'
1179:         terms = terms.join(' | ') if terms.is_a?(Array)
1180:         filter("to_tsvector(?::regconfig, ?) @@ to_tsquery(?::regconfig, ?)", lang, full_text_string_join(cols), lang, terms)
1181:       end

Insert given values into the database.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1184
1184:       def insert(*values)
1185:         if @opts[:returning]
1186:           # already know which columns to return, let the standard code
1187:           # handle it
1188:           super
1189:         elsif @opts[:sql]
1190:           # raw SQL used, so don't know which table is being inserted
1191:           # into, and therefore can't determine primary key.  Run the
1192:           # insert statement and return nil.
1193:           super
1194:           nil
1195:         else
1196:           # Force the use of RETURNING with the primary key value.
1197:           returning(insert_pk).insert(*values){|r| return r.values.first}
1198:         end
1199:       end

Insert a record returning the record inserted

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1202
1202:       def insert_select(*values)
1203:         returning.insert(*values){|r| return r}
1204:       end

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 1211
1211:       def lock(mode, opts=OPTS)
1212:         if block_given? # perform locking inside a transaction and yield to block
1213:           @db.transaction(opts){lock(mode, opts); yield}
1214:         else
1215:           sql = 'LOCK TABLE '
1216:           source_list_append(sql, @opts[:from])
1217:           mode = mode.to_s.upcase.strip
1218:           unless LOCK_MODES.include?(mode)
1219:             raise Error, "Unsupported lock mode: #{mode}"
1220:           end
1221:           sql << " IN #{mode} MODE"
1222:           @db.execute(sql, opts)
1223:         end
1224:         nil
1225:       end

PostgreSQL allows inserting multiple rows at once.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1228
1228:       def multi_insert_sql(columns, values)
1229:         sql = LiteralString.new('VALUES ')
1230:         expression_list_append(sql, values.map{|r| Array(r)})
1231:         [insert_sql(columns, sql)]
1232:       end

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 1236
1236:       def supports_cte_in_subqueries?
1237:         supports_cte?
1238:       end

DISTINCT ON is a PostgreSQL extension

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1241
1241:       def supports_distinct_on?
1242:         true
1243:       end

PostgreSQL supports modifying joined datasets

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1246
1246:       def supports_modifying_joins?
1247:         true
1248:       end

PostgreSQL supports pattern matching via regular expressions

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1256
1256:       def supports_regexp?
1257:         true
1258:       end

Returning is always supported.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1251
1251:       def supports_returning?(type)
1252:         true
1253:       end

PostgreSQL supports timezones in literal timestamps

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1261
1261:       def supports_timestamp_timezones?
1262:         true
1263:       end

PostgreSQL 8.4+ supports window functions

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1266
1266:       def supports_window_functions?
1267:         server_version >= 80400
1268:       end

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 1285
1285:       def truncate(opts = OPTS)
1286:         if opts.empty?
1287:           super()
1288:         else
1289:           clone(:truncate_opts=>opts).truncate
1290:         end
1291:       end

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 1294
1294:       def window(name, opts)
1295:         clone(:window=>(@opts[:window]||[]) + [[name, SQL::Window.new(opts)]])
1296:       end

Protected Instance methods

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 1304
1304:       def _import(columns, values, opts=OPTS)
1305:         if @opts[:returning]
1306:           statements = multi_insert_sql(columns, values)
1307:           @db.transaction(opts.merge(:server=>@opts[:server])) do
1308:             statements.map{|st| returning_fetch_rows(st)}
1309:           end.first.map{|v| v.length == 1 ? v.values.first : v}
1310:         elsif opts[:return] == :primary_key
1311:           returning(insert_pk)._import(columns, values, opts)
1312:         else
1313:           super
1314:         end
1315:       end

[Validate]