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
EXCLUSIVE = 'EXCLUSIVE'.freeze
EXPLAIN = 'EXPLAIN '.freeze
EXPLAIN_ANALYZE = 'EXPLAIN ANALYZE '.freeze
FOR_SHARE = ' FOR SHARE'.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
SHARE = 'SHARE'.freeze
SHARE_ROW_EXCLUSIVE = 'SHARE ROW EXCLUSIVE'.freeze
SHARE_UPDATE_EXCLUSIVE = 'SHARE UPDATE EXCLUSIVE'.freeze
SQL_WITH_RECURSIVE = "WITH RECURSIVE ".freeze
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
SELECT_VALUES = "VALUES ".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 1222
1222:       def analyze
1223:         explain(:analyze=>true)
1224:       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 1229
1229:       def complex_expression_sql_append(sql, op, args)
1230:         case op
1231:         when :^
1232:           j = XOR_OP
1233:           c = false
1234:           args.each do |a|
1235:             sql << j if c
1236:             literal_append(sql, a)
1237:             c ||= true
1238:           end
1239:         when :ILIKE, 'NOT ILIKE''NOT ILIKE'
1240:           sql << PAREN_OPEN
1241:           literal_append(sql, args.at(0))
1242:           sql << SPACE << op.to_s << SPACE
1243:           literal_append(sql, args.at(1))
1244:           sql << ESCAPE
1245:           literal_append(sql, BACKSLASH)
1246:           sql << PAREN_CLOSE
1247:         else
1248:           super
1249:         end
1250:       end

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).

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1266
1266:       def disable_insert_returning
1267:         clone(:disable_insert_returning=>true)
1268:       end

Return the results of an EXPLAIN query as a string

[Source]

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

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

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1276
1276:       def for_share
1277:         lock_style(:share)
1278:       end

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.
:rank :Set to true to order by the rank, so that closer matches are returned first.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1291
1291:       def full_text_search(cols, terms, opts = OPTS)
1292:         lang = Sequel.cast(opts[:language] || 'simple', :regconfig)
1293:         terms = terms.join(' | ') if terms.is_a?(Array)
1294:         columns = full_text_string_join(cols)
1295:         query_func = (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery
1296:         vector = Sequel.function(:to_tsvector, lang, columns)
1297:         query = Sequel.function(query_func, lang, terms)
1298: 
1299:         ds = where(Sequel.lit(["(", " @@ ", ")"], vector, query))
1300: 
1301:         if opts[:phrase]
1302:           ds = ds.grep(cols, "%#{escape_like(terms)}%", :case_insensitive=>true)
1303:         end
1304: 
1305:         if opts[:rank]
1306:           ds = ds.order{ts_rank_cd(vector, query)}
1307:         end
1308: 
1309:         ds
1310:       end

Insert given values into the database.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1313
1313:       def insert(*values)
1314:         if @opts[:returning]
1315:           # Already know which columns to return, let the standard code handle it
1316:           super
1317:         elsif @opts[:sql] || @opts[:disable_insert_returning]
1318:           # Raw SQL used or RETURNING disabled, just use the default behavior
1319:           # and return nil since sequence is not known.
1320:           super
1321:           nil
1322:         else
1323:           # Force the use of RETURNING with the primary key value,
1324:           # unless it has been disabled.
1325:           returning(insert_pk).insert(*values){|r| return r.values.first}
1326:         end
1327:       end

Insert a record returning the record inserted. Always returns nil without inserting a query if disable_insert_returning is used.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1331
1331:       def insert_select(*values)
1332:         return unless supports_insert_select?
1333:         server?(:default).with_sql_first(insert_select_sql(*values))
1334:       end

The SQL to use for an insert_select, adds a RETURNING clause to the insert unless the RETURNING clause is already present.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1338
1338:       def insert_select_sql(*values)
1339:         ds = opts[:returning] ? self : returning
1340:         ds.insert_sql(*values)
1341:       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 1348
1348:       def lock(mode, opts=OPTS)
1349:         if block_given? # perform locking inside a transaction and yield to block
1350:           @db.transaction(opts){lock(mode, opts); yield}
1351:         else
1352:           sql = 'LOCK TABLE '
1353:           source_list_append(sql, @opts[:from])
1354:           mode = mode.to_s.upcase.strip
1355:           unless LOCK_MODES.include?(mode)
1356:             raise Error, "Unsupported lock mode: #{mode}"
1357:           end
1358:           sql << " IN #{mode} MODE"
1359:           @db.execute(sql, opts)
1360:         end
1361:         nil
1362:       end

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1364
1364:       def supports_cte?(type=:select)
1365:         if type == :select
1366:           server_version >= 80400
1367:         else
1368:           server_version >= 90100
1369:         end
1370:       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 1374
1374:       def supports_cte_in_subqueries?
1375:         supports_cte?
1376:       end

DISTINCT ON is a PostgreSQL extension

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1379
1379:       def supports_distinct_on?
1380:         true
1381:       end

True unless insert returning has been disabled for this dataset.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1384
1384:       def supports_insert_select?
1385:         !@opts[:disable_insert_returning]
1386:       end

PostgreSQL 9.3rc1+ supports lateral subqueries

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1389
1389:       def supports_lateral_subqueries?
1390:         server_version >= 90300
1391:       end

PostgreSQL supports modifying joined datasets

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1394
1394:       def supports_modifying_joins?
1395:         true
1396:       end

PostgreSQL supports pattern matching via regular expressions

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1404
1404:       def supports_regexp?
1405:         true
1406:       end

Returning is always supported.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1399
1399:       def supports_returning?(type)
1400:         true
1401:       end

PostgreSQL supports timezones in literal timestamps

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1409
1409:       def supports_timestamp_timezones?
1410:         true
1411:       end

PostgreSQL 8.4+ supports window functions

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1414
1414:       def supports_window_functions?
1415:         server_version >= 80400
1416:       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 1433
1433:       def truncate(opts = OPTS)
1434:         if opts.empty?
1435:           super()
1436:         else
1437:           clone(:truncate_opts=>opts).truncate
1438:         end
1439:       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 1442
1442:       def window(name, opts)
1443:         clone(:window=>(@opts[:window]||[]) + [[name, SQL::Window.new(opts)]])
1444:       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 1452
1452:       def _import(columns, values, opts=OPTS)
1453:         if @opts[:returning]
1454:           statements = multi_insert_sql(columns, values)
1455:           @db.transaction(opts.merge(:server=>@opts[:server])) do
1456:             statements.map{|st| returning_fetch_rows(st)}
1457:           end.first.map{|v| v.length == 1 ? v.values.first : v}
1458:         elsif opts[:return] == :primary_key
1459:           returning(insert_pk)._import(columns, values, opts)
1460:         else
1461:           super
1462:         end
1463:       end

[Validate]