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')
- EXCLUSIVE =
'EXCLUSIVE'.freeze
- EXPLAIN =
'EXPLAIN '.freeze
- EXPLAIN_ANALYZE =
'EXPLAIN ANALYZE '.freeze
- FOR_SHARE =
' FOR SHARE'.freeze
- 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')
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) ⇒ Object
Insert given values into the database.
-
#insert_returning_sql(returning, *values) ⇒ Object
Use the RETURNING clause to return the columns listed in returning.
-
#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_distinct_on? ⇒ Boolean
DISTINCT ON is a PostgreSQL extension.
-
#supports_modifying_joins? ⇒ Boolean
PostgreSQL supports modifying joined datasets.
-
#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
664 665 666 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 664 def self.extended(obj) obj.def_mutation_method(:disable_insert_returning) end |
.included(mod) ⇒ Object
Add the disable_insert_returning! mutation method
669 670 671 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 669 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
674 675 676 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 674 def analyze explain(:analyze=>true) end |
#complex_expression_sql(op, args) ⇒ Object
Handle converting the ruby xor operator (^) into the PostgreSQL xor operator (#).
680 681 682 683 684 685 686 687 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 680 def complex_expression_sql(op, args) case op when :^ "(#{literal(args.at(0))} # #{literal(args.at(1))})" else super end end |
#disable_insert_returning ⇒ Object
Disable the use of INSERT RETURNING, even if the server supports it
690 691 692 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 690 def disable_insert_returning clone(:disable_insert_returning=>true) end |
#explain(opts = {}) ⇒ Object
Return the results of an EXPLAIN query as a string
695 696 697 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 695 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.
700 701 702 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 700 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).
706 707 708 709 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 706 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) ⇒ Object
Insert given values into the database.
712 713 714 715 716 717 718 719 720 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 712 def insert(*values) if @opts[:sql] execute_insert(insert_sql(*values)) elsif @opts[:disable_insert_returning] || server_version < 80200 execute_insert(insert_sql(*values), :table=>opts[:from].first, :values=>values.size == 1 ? values.first : values) else clone(default_server_opts(:sql=>insert_returning_pk_sql(*values))).single_value end end |
#insert_returning_sql(returning, *values) ⇒ Object
Use the RETURNING clause to return the columns listed in returning.
723 724 725 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 723 def insert_returning_sql(returning, *values) "#{insert_sql(*values)} RETURNING #{column_list(Array(returning))}" end |
#insert_select(*values) ⇒ Object
Insert a record returning the record inserted
728 729 730 731 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 728 def insert_select(*values) return if opts[:disable_insert_returning] || server_version < 80200 naked.clone(default_server_opts(:sql=>insert_returning_sql(nil, *values))).single_record 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.
738 739 740 741 742 743 744 745 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 738 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.
748 749 750 751 752 753 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 748 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_distinct_on? ⇒ Boolean
DISTINCT ON is a PostgreSQL extension
756 757 758 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 756 def supports_distinct_on? true end |
#supports_modifying_joins? ⇒ Boolean
PostgreSQL supports modifying joined datasets
761 762 763 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 761 def true end |
#supports_timestamp_timezones? ⇒ Boolean
PostgreSQL supports timezones in literal timestamps
766 767 768 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 766 def true end |
#supports_window_functions? ⇒ Boolean
PostgreSQL 8.4+ supports window functions
771 772 773 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 771 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.
776 777 778 |
# File 'lib/sequel/adapters/shared/postgres.rb', line 776 def window(name, opts) clone(:window=>(@opts[:window]||[]) + [[name, SQL::Window.new(opts)]]) end |