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

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

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

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

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

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

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

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

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

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

PostgreSQL supports modifying joined datasets

[Source]

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

Returning is always supported.

[Source]

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

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

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

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

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

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]