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'from using where')
- DELETE_CLAUSE_METHODS_91 =
Dataset.clause_methods(:delete, %w'with 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'into columns values')
- INSERT_CLAUSE_METHODS_82 =
Dataset.clause_methods(:insert, %w'into columns values returning')
- INSERT_CLAUSE_METHODS_91 =
Dataset.clause_methods(:insert, %w'with 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'distinct columns from join where group having compounds order limit lock')
- SELECT_CLAUSE_METHODS_84 =
Dataset.clause_methods(:select, %w'with 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'table set from where')
- UPDATE_CLAUSE_METHODS_91 =
Dataset.clause_methods(:update, %w'with table set from where returning')
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(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
685 686 687 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 685 def self.extended(obj) obj.def_mutation_method(:disable_insert_returning) end |
.included(mod) ⇒ Object
Add the disable_insert_returning! mutation method
690 691 692 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 690 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
695 696 697 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 695 def analyze explain(:analyze=>true) end |
#complex_expression_sql(op, args) ⇒ Object
Handle converting the ruby xor operator (^) into the PostgreSQL xor operator (#).
701 702 703 704 705 706 707 708 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 701 def complex_expression_sql(op, args) case op when :^ "(#{args.collect{|a| literal(a)}.join(" # ")})" else super end end |
#disable_insert_returning ⇒ Object
Disable the use of INSERT RETURNING, even if the server supports it
711 712 713 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 711 def disable_insert_returning clone(:disable_insert_returning=>true) end |
#explain(opts = {}) ⇒ Object
Return the results of an EXPLAIN query as a string
716 717 718 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 716 def explain(opts={}) with_sql((opts[:analyze] ? EXPLAIN_ANALYZE : EXPLAIN) + select_sql).map(QUERY_PLAN).join("\r\n") end |
#for_share ⇒ Object
Return a cloned dataset which will use FOR SHARE to lock returned rows.
721 722 723 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 721 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).
727 728 729 730 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 727 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.
733 734 735 736 737 738 739 740 741 742 743 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 733 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
746 747 748 749 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 746 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.
756 757 758 759 760 761 762 763 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 756 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.
766 767 768 769 770 771 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 766 def multi_insert_sql(columns, values) return super if server_version < 80200 # postgresql 8.2 introduces support for multi-row insert [insert_sql(columns, LiteralString.new('VALUES ' + values.map {|r| literal(Array(r))}.join(COMMA_SEPARATOR)))] 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+).
775 776 777 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 775 def supports_cte_in_subqueries? supports_cte? end |
#supports_distinct_on? ⇒ Boolean
DISTINCT ON is a PostgreSQL extension
780 781 782 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 780 def supports_distinct_on? true end |
#supports_modifying_joins? ⇒ Boolean
PostgreSQL supports modifying joined datasets
785 786 787 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 785 def true end |
#supports_returning?(type) ⇒ Boolean
789 790 791 792 793 794 795 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 789 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
798 799 800 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 798 def true end |
#supports_window_functions? ⇒ Boolean
PostgreSQL 8.4+ supports window functions
803 804 805 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 803 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.
808 809 810 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 808 def window(name, opts) clone(:window=>(@opts[:window]||[]) + [[name, SQL::Window.new(opts)]]) end |