Module: Sequel::Postgres::DatasetMethods
- Included in:
- DataObjects::Postgres::Dataset, JDBC::Postgres::Dataset, Dataset, Swift::Postgres::Dataset
- Defined in:
- lib/sequel/adapters/shared/postgres.rb
Overview
Instance methods for datasets that connect to a PostgreSQL database.
Defined Under Namespace
Modules: PreparedStatementMethods
Constant Summary collapse
- 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')
- 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')
- INSERT_CLAUSE_METHODS_82 =
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')
- 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
Class Method Summary collapse
-
.extended(obj) ⇒ Object
Add the disable_insert_returning! mutation method.
-
.included(mod) ⇒ Object
Add the disable_insert_returning! mutation method.
Instance Method Summary collapse
-
#analyze ⇒ Object
Return the results of an ANALYZE query as a string.
-
#complex_expression_sql_append(sql, op, args) ⇒ Object
Handle converting the ruby xor operator (^) into the PostgreSQL xor operator (#).
-
#disable_insert_returning ⇒ Object
Disable the use of INSERT RETURNING, even if the server supports it.
-
#explain(opts = {}) ⇒ Object
Return the results of an EXPLAIN query as a string.
-
#for_share ⇒ Object
Return a cloned dataset which will use FOR SHARE to lock returned rows.
-
#full_text_search(cols, terms, opts = {}) ⇒ Object
PostgreSQL specific full text search syntax, using tsearch2 (included in 8.3 by default, and available for earlier versions as an add-on).
-
#insert(*values, &block) ⇒ Object
Insert given values into the database.
-
#insert_select(*values) ⇒ Object
Insert a record returning the record inserted.
-
#lock(mode, opts = {}) ⇒ Object
Locks all tables in the dataset’s FROM clause (but not in JOINs) with the specified mode (e.g. ‘EXCLUSIVE’).
-
#multi_insert_sql(columns, values) ⇒ Object
For PostgreSQL version > 8.2, allow inserting multiple rows at once.
-
#supports_cte_in_subqueries? ⇒ Boolean
PostgreSQL supports using the WITH clause in subqueries if it supports using WITH at all (i.e. on PostgreSQL 8.4+).
-
#supports_distinct_on? ⇒ Boolean
DISTINCT ON is a PostgreSQL extension.
-
#supports_modifying_joins? ⇒ Boolean
PostgreSQL supports modifying joined datasets.
- #supports_returning?(type) ⇒ Boolean
-
#supports_timestamp_timezones? ⇒ Boolean
PostgreSQL supports timezones in literal timestamps.
-
#supports_window_functions? ⇒ Boolean
PostgreSQL 8.4+ supports window functions.
-
#window(name, opts) ⇒ Object
Return a clone of the dataset with an addition named window that can be referenced in window functions.
Class Method Details
.extended(obj) ⇒ Object
Add the disable_insert_returning! mutation method
699 700 701 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 699 def self.extended(obj) obj.def_mutation_method(:disable_insert_returning) end |
.included(mod) ⇒ Object
Add the disable_insert_returning! mutation method
704 705 706 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 704 def self.included(mod) mod.def_mutation_method(:disable_insert_returning) end |
Instance Method Details
#analyze ⇒ Object
Return the results of an ANALYZE query as a string
709 710 711 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 709 def analyze explain(:analyze=>true) end |
#complex_expression_sql_append(sql, op, args) ⇒ Object
Handle converting the ruby xor operator (^) into the PostgreSQL xor operator (#).
715 716 717 718 719 720 721 722 723 724 725 726 727 728 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 715 def complex_expression_sql_append(sql, op, args) case op when :^ j = XOR_OP c = false args.each do |a| sql << j if c literal_append(sql, a) c ||= true end else super end end |
#disable_insert_returning ⇒ Object
Disable the use of INSERT RETURNING, even if the server supports it
731 732 733 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 731 def disable_insert_returning clone(:disable_insert_returning=>true) end |
#explain(opts = {}) ⇒ Object
Return the results of an EXPLAIN query as a string
736 737 738 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 736 def explain(opts={}) with_sql((opts[:analyze] ? EXPLAIN_ANALYZE : EXPLAIN) + select_sql).map(QUERY_PLAN).join(CRLF) end |
#for_share ⇒ Object
Return a cloned dataset which will use FOR SHARE to lock returned rows.
741 742 743 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 741 def for_share lock_style(:share) end |
#full_text_search(cols, terms, opts = {}) ⇒ Object
PostgreSQL specific full text search syntax, using tsearch2 (included in 8.3 by default, and available for earlier versions as an add-on).
747 748 749 750 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 747 def full_text_search(cols, terms, opts = {}) lang = opts[:language] || 'simple' filter("to_tsvector(#{literal(lang)}, #{full_text_string_join(cols)}) @@ to_tsquery(#{literal(lang)}, #{literal(Array(terms).join(' | '))})") end |
#insert(*values, &block) ⇒ Object
Insert given values into the database.
753 754 755 756 757 758 759 760 761 762 763 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 753 def insert(*values, &block) if @opts[:returning] super elsif !@opts[:sql] && supports_insert_select? returning(insert_pk).insert(*values){|r| return r.values.first} elsif (f = opts[:from]) && !f.empty? execute_insert(insert_sql(*values), :table=>f.first, :values=>values.size == 1 ? values.first : values) else super end end |
#insert_select(*values) ⇒ Object
Insert a record returning the record inserted
766 767 768 769 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 766 def insert_select(*values) return unless supports_insert_select? returning.insert(*values){|r| return r} end |
#lock(mode, opts = {}) ⇒ Object
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.
776 777 778 779 780 781 782 783 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 776 def lock(mode, opts={}) if block_given? # perform locking inside a transaction and yield to block @db.transaction(opts){lock(mode, opts); yield} else @db.execute(LOCK % [source_list(@opts[:from]), mode], opts) # lock without a transaction end nil end |
#multi_insert_sql(columns, values) ⇒ Object
For PostgreSQL version > 8.2, allow inserting multiple rows at once.
786 787 788 789 790 791 792 793 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 786 def multi_insert_sql(columns, values) return super if server_version < 80200 # postgresql 8.2 introduces support for multi-row insert sql = LiteralString.new('VALUES ') expression_list_append(sql, values.map{|r| Array(r)}) [insert_sql(columns, sql)] end |
#supports_cte_in_subqueries? ⇒ Boolean
PostgreSQL supports using the WITH clause in subqueries if it supports using WITH at all (i.e. on PostgreSQL 8.4+).
797 798 799 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 797 def supports_cte_in_subqueries? supports_cte? end |
#supports_distinct_on? ⇒ Boolean
DISTINCT ON is a PostgreSQL extension
802 803 804 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 802 def supports_distinct_on? true end |
#supports_modifying_joins? ⇒ Boolean
PostgreSQL supports modifying joined datasets
807 808 809 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 807 def true end |
#supports_returning?(type) ⇒ Boolean
811 812 813 814 815 816 817 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 811 def supports_returning?(type) if type == :insert server_version >= 80200 && !opts[:disable_insert_returning] else server_version >= 90100 end end |
#supports_timestamp_timezones? ⇒ Boolean
PostgreSQL supports timezones in literal timestamps
820 821 822 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 820 def true end |
#supports_window_functions? ⇒ Boolean
PostgreSQL 8.4+ supports window functions
825 826 827 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 825 def supports_window_functions? server_version >= 80400 end |
#window(name, opts) ⇒ Object
Return a clone of the dataset with an addition named window that can be referenced in window functions.
830 831 832 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 830 def window(name, opts) clone(:window=>(@opts[:window]||[]) + [[name, SQL::Window.new(opts)]]) end |