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

Methods

Included Modules

UnmodifiedIdentifiers::DatasetMethods

Constants

NULL = LiteralString.new('NULL').freeze
LOCK_MODES = ['ACCESS SHARE', 'ROW SHARE', 'ROW EXCLUSIVE', 'SHARE UPDATE EXCLUSIVE', 'SHARE', 'SHARE ROW EXCLUSIVE', 'EXCLUSIVE', 'ACCESS EXCLUSIVE'].each(&:freeze).freeze

Public Instance methods

Return the results of an EXPLAIN ANALYZE query as a string

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1359
1359:       def analyze
1360:         explain(:analyze=>true)
1361:       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 1366
1366:       def complex_expression_sql_append(sql, op, args)
1367:         case op
1368:         when :^
1369:           j = ' # '
1370:           c = false
1371:           args.each do |a|
1372:             sql << j if c
1373:             literal_append(sql, a)
1374:             c ||= true
1375:           end
1376:         when :ILIKE, 'NOT ILIKE''NOT ILIKE'
1377:           sql << '('
1378:           literal_append(sql, args[0])
1379:           sql << ' ' << op.to_s << ' '
1380:           literal_append(sql, args[1])
1381:           sql << " ESCAPE "
1382:           literal_append(sql, "\\")
1383:           sql << ')'
1384:         else
1385:           super
1386:         end
1387:       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 1403
1403:       def disable_insert_returning
1404:         clone(:disable_insert_returning=>true)
1405:       end

Return the results of an EXPLAIN query as a string

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1408
1408:       def explain(opts=OPTS)
1409:         with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map('QUERY PLAN''QUERY PLAN').join("\r\n")
1410:       end

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

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1413
1413:       def for_share
1414:         lock_style(:share)
1415:       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:

:headline :Append a expression to the selected columns aliased to headline that contains an extract of the matched text.
: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.
:to_tsquery :Can be set to :plain or :phrase to specify the function to use to convert the terms to a ts_query.
:tsquery :Specifies the terms argument is already a valid SQL expression returning a tsquery, and can be used directly in the query.
:tsvector :Specifies the cols argument is already a valid SQL expression returning a tsvector, and can be used directly in the query.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1436
1436:       def full_text_search(cols, terms, opts = OPTS)
1437:         lang = Sequel.cast(opts[:language] || 'simple', :regconfig)
1438: 
1439:         unless opts[:tsvector]
1440:           phrase_cols = full_text_string_join(cols)
1441:           cols = Sequel.function(:to_tsvector, lang, phrase_cols)
1442:         end
1443: 
1444:         unless opts[:tsquery]
1445:           phrase_terms = terms.is_a?(Array) ? terms.join(' | ') : terms
1446: 
1447:           query_func = case to_tsquery = opts[:to_tsquery]
1448:           when :phrase, :plain
1449:             "#{to_tsquery}to_tsquery""#{to_tsquery}to_tsquery"
1450:           else
1451:             (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery
1452:           end
1453: 
1454:           terms = Sequel.function(query_func, lang, phrase_terms)
1455:         end
1456: 
1457:         ds = where(Sequel.lit(["", " @@ ", ""], cols, terms))
1458: 
1459:         if opts[:phrase]
1460:           raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery]
1461:           ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true)
1462:         end
1463: 
1464:         if opts[:rank]
1465:           ds = ds.reverse{ts_rank_cd(cols, terms)}
1466:         end
1467: 
1468:         if opts[:headline]
1469:           ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)}
1470:         end
1471: 
1472:         ds
1473:       end

Insert given values into the database.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1476
1476:       def insert(*values)
1477:         if @opts[:returning]
1478:           # Already know which columns to return, let the standard code handle it
1479:           super
1480:         elsif @opts[:sql] || @opts[:disable_insert_returning]
1481:           # Raw SQL used or RETURNING disabled, just use the default behavior
1482:           # and return nil since sequence is not known.
1483:           super
1484:           nil
1485:         else
1486:           # Force the use of RETURNING with the primary key value,
1487:           # unless it has been disabled.
1488:           returning(insert_pk).insert(*values){|r| return r.values.first}
1489:         end
1490:       end

Handle uniqueness violations when inserting, by updating the conflicting row, using ON CONFLICT. With no options, uses ON CONFLICT DO NOTHING. Options:

:conflict_where :The index filter, when using a partial index to determine uniqueness.
:constraint :An explicit constraint name, has precendence over :target.
:target :The column name or expression to handle uniqueness violations on.
:update :A hash of columns and values to set. Uses ON CONFLICT DO UPDATE.
:update_where :A WHERE condition to use for the update.

Examples:

  DB[:table].insert_conflict.insert(a: 1, b: 2)
  # INSERT INTO TABLE (a, b) VALUES (1, 2)
  # ON CONFLICT DO NOTHING

  DB[:table].insert_conflict(constraint: :table_a_uidx).insert(a: 1, b: 2)
  # INSERT INTO TABLE (a, b) VALUES (1, 2)
  # ON CONFLICT ON CONSTRAINT table_a_uidx DO NOTHING

  DB[:table].insert_conflict(target: :a).insert(a: 1, b: 2)
  # INSERT INTO TABLE (a, b) VALUES (1, 2)
  # ON CONFLICT (a) DO NOTHING

  DB[:table].insert_conflict(target: :a, conflict_where: {c: true}).insert(a: 1, b: 2)
  # INSERT INTO TABLE (a, b) VALUES (1, 2)
  # ON CONFLICT (a) WHERE (c IS TRUE) DO NOTHING

  DB[:table].insert_conflict(target: :a, update: {b: Sequel[:excluded][:b]}).insert(a: 1, b: 2)
  # INSERT INTO TABLE (a, b) VALUES (1, 2)
  # ON CONFLICT (a) DO UPDATE SET b = excluded.b

  DB[:table].insert_conflict(constraint: :table_a_uidx,
    update: {b: Sequel[:excluded][:b]}, update_where: {Sequel[:table][:status_id] => 1}).insert(a: 1, b: 2)
  # INSERT INTO TABLE (a, b) VALUES (1, 2)
  # ON CONFLICT ON CONSTRAINT table_a_uidx
  # DO UPDATE SET b = excluded.b WHERE (table.status_id = 1)

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1527
1527:       def insert_conflict(opts=OPTS)
1528:         clone(:insert_conflict => opts)
1529:       end

Ignore uniqueness/exclusion violations when inserting, using ON CONFLICT DO NOTHING. Exists mostly for compatibility to MySQL‘s insert_ignore. Example:

  DB[:table].insert_ignore.insert(a: 1, b: 2)
  # INSERT INTO TABLE (a, b) VALUES (1, 2)
  # ON CONFLICT DO NOTHING

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1537
1537:       def insert_ignore
1538:         insert_conflict
1539:       end

Insert a record, returning the record inserted, using RETURNING. Always returns nil without running an INSERT statement if disable_insert_returning is used. If the query runs but returns no values, returns false.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1544
1544:       def insert_select(*values)
1545:         return unless supports_insert_select?
1546:         # Handle case where query does not return a row
1547:         server?(:default).with_sql_first(insert_select_sql(*values)) || false
1548:       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 1552
1552:       def insert_select_sql(*values)
1553:         ds = opts[:returning] ? self : returning
1554:         ds.insert_sql(*values)
1555:       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 1562
1562:       def lock(mode, opts=OPTS)
1563:         if block_given? # perform locking inside a transaction and yield to block
1564:           @db.transaction(opts){lock(mode, opts); yield}
1565:         else
1566:           sql = 'LOCK TABLE '.dup
1567:           source_list_append(sql, @opts[:from])
1568:           mode = mode.to_s.upcase.strip
1569:           unless LOCK_MODES.include?(mode)
1570:             raise Error, "Unsupported lock mode: #{mode}"
1571:           end
1572:           sql << " IN #{mode} MODE"
1573:           @db.execute(sql, opts)
1574:         end
1575:         nil
1576:       end

Use OVERRIDING USER VALUE for INSERT statements, so that identity columns always use the user supplied value, and an error is not raised for identity columns that are GENERATED ALWAYS.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1581
1581:       def overriding_system_value
1582:         clone(:override=>:system)
1583:       end

Use OVERRIDING USER VALUE for INSERT statements, so that identity columns always use the sequence value instead of the user supplied value.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1587
1587:       def overriding_user_value
1588:         clone(:override=>:user)
1589:       end

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1591
1591:       def supports_cte?(type=:select)
1592:         if type == :select
1593:           server_version >= 80400
1594:         else
1595:           server_version >= 90100
1596:         end
1597:       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 1601
1601:       def supports_cte_in_subqueries?
1602:         supports_cte?
1603:       end

DISTINCT ON is a PostgreSQL extension

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1606
1606:       def supports_distinct_on?
1607:         true
1608:       end

PostgreSQL 9.5+ supports GROUP CUBE

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1611
1611:       def supports_group_cube?
1612:         server_version >= 90500
1613:       end

PostgreSQL 9.5+ supports GROUP ROLLUP

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1616
1616:       def supports_group_rollup?
1617:         server_version >= 90500
1618:       end

PostgreSQL 9.5+ supports GROUPING SETS

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1621
1621:       def supports_grouping_sets?
1622:         server_version >= 90500
1623:       end

PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1631
1631:       def supports_insert_conflict?
1632:         server_version >= 90500
1633:       end

True unless insert returning has been disabled for this dataset.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1626
1626:       def supports_insert_select?
1627:         !@opts[:disable_insert_returning]
1628:       end

PostgreSQL 9.3+ supports lateral subqueries

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1636
1636:       def supports_lateral_subqueries?
1637:         server_version >= 90300
1638:       end

PostgreSQL supports modifying joined datasets

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1641
1641:       def supports_modifying_joins?
1642:         true
1643:       end

PostgreSQL supports NOWAIT.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1646
1646:       def supports_nowait?
1647:         true
1648:       end

PostgreSQL supports pattern matching via regular expressions

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1656
1656:       def supports_regexp?
1657:         true
1658:       end

Returning is always supported.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1651
1651:       def supports_returning?(type)
1652:         true
1653:       end

PostgreSQL 9.5+ supports SKIP LOCKED.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1661
1661:       def supports_skip_locked?
1662:         server_version >= 90500
1663:       end

PostgreSQL supports timezones in literal timestamps

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1666
1666:       def supports_timestamp_timezones?
1667:         true
1668:       end

PostgreSQL 8.4+ supports window functions

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1671
1671:       def supports_window_functions?
1672:         server_version >= 80400
1673:       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"

  DB[:table].truncate(cascade: true, only: true, restart: true)
  # TRUNCATE TABLE ONLY "table" RESTART IDENTITY CASCADE

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1691
1691:       def truncate(opts = OPTS)
1692:         if opts.empty?
1693:           super()
1694:         else
1695:           clone(:truncate_opts=>opts).truncate
1696:         end
1697:       end

Return a clone of the dataset with an addition named window that can be referenced in window functions. See Sequel::SQL::Window for a list of options that can be passed in.

[Source]

      # File lib/sequel/adapters/shared/postgres.rb, line 1702
1702:       def window(name, opts)
1703:         clone(:window=>(@opts[:window]||[]) + [[name, SQL::Window.new(opts)]])
1704:       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 1712
1712:       def _import(columns, values, opts=OPTS)
1713:         if @opts[:returning]
1714:           statements = multi_insert_sql(columns, values)
1715:           @db.transaction(Hash[opts].merge!(:server=>@opts[:server])) do
1716:             statements.map{|st| returning_fetch_rows(st)}
1717:           end.first.map{|v| v.length == 1 ? v.values.first : v}
1718:         elsif opts[:return] == :primary_key
1719:           returning(insert_pk)._import(columns, values, opts)
1720:         else
1721:           super
1722:         end
1723:       end

[Validate]