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 802
802:       def analyze
803:         explain(:analyze=>true)
804:       end

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

[Source]

     # File lib/sequel/adapters/shared/postgres.rb, line 808
808:       def complex_expression_sql_append(sql, op, args)
809:         case op
810:         when :^
811:           j = XOR_OP
812:           c = false
813:           args.each do |a|
814:             sql << j if c
815:             literal_append(sql, a)
816:             c ||= true
817:           end
818:         else
819:           super
820:         end
821:       end

Return the results of an EXPLAIN query as a string

[Source]

     # File lib/sequel/adapters/shared/postgres.rb, line 824
824:       def explain(opts={})
825:         with_sql((opts[:analyze] ? EXPLAIN_ANALYZE : EXPLAIN) + select_sql).map(QUERY_PLAN).join(CRLF)
826:       end

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

[Source]

     # File lib/sequel/adapters/shared/postgres.rb, line 829
829:       def for_share
830:         lock_style(:share)
831:       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 835
835:       def full_text_search(cols, terms, opts = {})
836:         lang = opts[:language] || 'simple'
837:         terms = terms.join(' | ') if terms.is_a?(Array)
838:         filter("to_tsvector(?::regconfig, ?) @@ to_tsquery(?::regconfig, ?)", lang, full_text_string_join(cols), lang, terms)
839:       end

Insert given values into the database.

[Source]

     # File lib/sequel/adapters/shared/postgres.rb, line 842
842:       def insert(*values)
843:         if @opts[:returning]
844:           # already know which columns to return, let the standard code
845:           # handle it
846:           super
847:         elsif @opts[:sql]
848:           # raw SQL used, so don't know which table is being inserted
849:           # into, and therefore can't determine primary key.  Run the
850:           # insert statement and return nil.
851:           super
852:           nil
853:         else
854:           # Force the use of RETURNING with the primary key value.
855:           returning(insert_pk).insert(*values){|r| return r.values.first}
856:         end
857:       end

Insert a record returning the record inserted

[Source]

     # File lib/sequel/adapters/shared/postgres.rb, line 860
860:       def insert_select(*values)
861:         returning.insert(*values){|r| return r}
862:       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 869
869:       def lock(mode, opts={})
870:         if block_given? # perform locking inside a transaction and yield to block
871:           @db.transaction(opts){lock(mode, opts); yield}
872:         else
873:           @db.execute(LOCK % [source_list(@opts[:from]), mode], opts) # lock without a transaction
874:         end
875:         nil
876:       end

PostgreSQL allows inserting multiple rows at once.

[Source]

     # File lib/sequel/adapters/shared/postgres.rb, line 879
879:       def multi_insert_sql(columns, values)
880:         sql = LiteralString.new('VALUES ')
881:         expression_list_append(sql, values.map{|r| Array(r)})
882:         [insert_sql(columns, sql)]
883:       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 887
887:       def supports_cte_in_subqueries?
888:         supports_cte?
889:       end

DISTINCT ON is a PostgreSQL extension

[Source]

     # File lib/sequel/adapters/shared/postgres.rb, line 892
892:       def supports_distinct_on?
893:         true
894:       end

PostgreSQL supports modifying joined datasets

[Source]

     # File lib/sequel/adapters/shared/postgres.rb, line 897
897:       def supports_modifying_joins?
898:         true
899:       end

Returning is always supported.

[Source]

     # File lib/sequel/adapters/shared/postgres.rb, line 902
902:       def supports_returning?(type)
903:         true
904:       end

PostgreSQL supports timezones in literal timestamps

[Source]

     # File lib/sequel/adapters/shared/postgres.rb, line 907
907:       def supports_timestamp_timezones?
908:         true
909:       end

PostgreSQL 8.4+ supports window functions

[Source]

     # File lib/sequel/adapters/shared/postgres.rb, line 912
912:       def supports_window_functions?
913:         server_version >= 80400
914:       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 931
931:       def truncate(opts = {})
932:         if opts.empty?
933:           super()
934:         else
935:           clone(:truncate_opts=>opts).truncate
936:         end
937:       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 940
940:       def window(name, opts)
941:         clone(:window=>(@opts[:window]||[]) + [[name, SQL::Window.new(opts)]])
942:       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 950
950:       def _import(columns, values, opts={})
951:         if @opts[:returning]
952:           statements = multi_insert_sql(columns, values)
953:           @db.transaction(opts.merge(:server=>@opts[:server])) do
954:             statements.map{|st| returning_fetch_rows(st)}
955:           end.first.map{|v| v.length == 1 ? v.values.first : v}
956:         elsif opts[:return] == :primary_key
957:           returning(insert_pk)._import(columns, values, opts)
958:         else
959:           super
960:         end
961:       end

[Validate]