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_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')
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

### Public Instance methods

analyze()

Return the results of an EXPLAIN ANALYZE query as a string

[Source]

         # File lib/sequel/adapters/shared/postgres.rb, line 914
    914:       def analyze
    915:         explain(:analyze=>true)
    916:       end

complex_expression_sql_append(sql, op, args)

Handle converting the ruby xor operator (^) into the PostgreSQL xor operator (#).

[Source]

         # File lib/sequel/adapters/shared/postgres.rb, line 920
    920:       def complex_expression_sql_append(sql, op, args)
    921:         case op
    922:         when :^
    923:           j = XOR_OP
    924:           c = false
    925:           args.each do |a|
    926:             sql << j if c
    927:             literal_append(sql, a)
    928:             c ||= true
    929:           end
    930:         else
    931:           super
    932:         end
    933:       end

explain(opts={})

Return the results of an EXPLAIN query as a string

[Source]

         # File lib/sequel/adapters/shared/postgres.rb, line 936
    936:       def explain(opts={})
    937:         with_sql((opts[:analyze] ? EXPLAIN_ANALYZE : EXPLAIN) + select_sql).map(QUERY_PLAN).join(CRLF)
    938:       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 941
    941:       def for_share
    942:         lock_style(:share)
    943:       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 947
    947:       def full_text_search(cols, terms, opts = {})
    948:         lang = opts[:language] || 'simple'
    949:         terms = terms.join(' | ') if terms.is_a?(Array)
    950:         filter("to_tsvector(?::regconfig, ?) @@ to_tsquery(?::regconfig, ?)", lang, full_text_string_join(cols), lang, terms)
    951:       end

insert(*values)

Insert given values into the database.

[Source]

         # File lib/sequel/adapters/shared/postgres.rb, line 954
    954:       def insert(*values)
    955:         if @opts[:returning]
    956:           # already know which columns to return, let the standard code
    957:           # handle it
    958:           super
    959:         elsif @opts[:sql]
    960:           # raw SQL used, so don't know which table is being inserted
    961:           # into, and therefore can't determine primary key.  Run the
    962:           # insert statement and return nil.
    963:           super
    964:           nil
    965:         else
    966:           # Force the use of RETURNING with the primary key value.
    967:           returning(insert_pk).insert(*values){|r| return r.values.first}
    968:         end
    969:       end

insert_select(*values)

Insert a record returning the record inserted

[Source]

         # File lib/sequel/adapters/shared/postgres.rb, line 972
    972:       def insert_select(*values)
    973:         returning.insert(*values){|r| return r}
    974:       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 981
    981:       def lock(mode, opts={})
    982:         if block_given? # perform locking inside a transaction and yield to block
    983:           @db.transaction(opts){lock(mode, opts); yield}
    984:         else
    985:           @db.execute(LOCK % [source_list(@opts[:from]), mode], opts) # lock without a transaction
    986:         end
    987:         nil
    988:       end

multi_insert_sql(columns, values)

PostgreSQL allows inserting multiple rows at once.

[Source]

         # File lib/sequel/adapters/shared/postgres.rb, line 991
    991:       def multi_insert_sql(columns, values)
    992:         sql = LiteralString.new('VALUES ')
    993:         expression_list_append(sql, values.map{|r| Array(r)})
    994:         [insert_sql(columns, sql)]
    995:       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 999
     999:       def supports_cte_in_subqueries?
    1000:         supports_cte?
    1001:       end

supports_distinct_on?()

DISTINCT ON is a PostgreSQL extension

[Source]

          # File lib/sequel/adapters/shared/postgres.rb, line 1004
    1004:       def supports_distinct_on?
    1005:         true
    1006:       end

supports_modifying_joins?()

PostgreSQL supports modifying joined datasets

[Source]

          # File lib/sequel/adapters/shared/postgres.rb, line 1009
    1009:       def supports_modifying_joins?
    1010:         true
    1011:       end

supports_returning?(type)

Returning is always supported.

[Source]

          # File lib/sequel/adapters/shared/postgres.rb, line 1014
    1014:       def supports_returning?(type)
    1015:         true
    1016:       end

supports_timestamp_timezones?()

PostgreSQL supports timezones in literal timestamps

[Source]

          # File lib/sequel/adapters/shared/postgres.rb, line 1019
    1019:       def supports_timestamp_timezones?
    1020:         true
    1021:       end

supports_window_functions?()

PostgreSQL 8.4+ supports window functions

[Source]

          # File lib/sequel/adapters/shared/postgres.rb, line 1024
    1024:       def supports_window_functions?
    1025:         server_version >= 80400
    1026:       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 1043
    1043:       def truncate(opts = {})
    1044:         if opts.empty?
    1045:           super()
    1046:         else
    1047:           clone(:truncate_opts=>opts).truncate
    1048:         end
    1049:       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 1052
    1052:       def window(name, opts)
    1053:         clone(:window=>(@opts[:window]||[]) + [[name, SQL::Window.new(opts)]])
    1054:       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 1062
    1062:       def _import(columns, values, opts={})
    1063:         if @opts[:returning]
    1064:           statements = multi_insert_sql(columns, values)
    1065:           @db.transaction(opts.merge(:server=>@opts[:server])) do
    1066:             statements.map{|st| returning_fetch_rows(st)}
    1067:           end.first.map{|v| v.length == 1 ? v.values.first : v}
    1068:         elsif opts[:return] == :primary_key
    1069:           returning(insert_pk)._import(columns, values, opts)
    1070:         else
    1071:           super
    1072:         end
    1073:       end

[Validate]