module Sequel::Postgres::DatasetMethods

Constants

EXPLAIN_BOOLEAN_OPTIONS
EXPLAIN_NONBOOLEAN_OPTIONS
LOCK_MODES
NULL

Public Instance Methods

analyze() click to toggle source

Return the results of an EXPLAIN ANALYZE query as a string

     # File lib/sequel/adapters/shared/postgres.rb
1914 def analyze
1915   explain(:analyze=>true)
1916 end
complex_expression_sql_append(sql, op, args) click to toggle source

Handle converting the ruby xor operator (^) into the PostgreSQL xor operator (#), and use the ILIKE and NOT ILIKE operators.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1921 def complex_expression_sql_append(sql, op, args)
1922   case op
1923   when :^
1924     j = ' # '
1925     c = false
1926     args.each do |a|
1927       sql << j if c
1928       literal_append(sql, a)
1929       c ||= true
1930     end
1931   when :ILIKE, :'NOT ILIKE'
1932     sql << '('
1933     literal_append(sql, args[0])
1934     sql << ' ' << op.to_s << ' '
1935     literal_append(sql, args[1])
1936     sql << ')'
1937   else
1938     super
1939   end
1940 end
disable_insert_returning() click to toggle source

Disables automatic use of INSERT … RETURNING. You can still use returning manually to force the use of RETURNING when inserting.

This is designed for cases where INSERT RETURNING cannot be used, such as when you are using partitioning with trigger functions or conditional rules, or when you are using a PostgreSQL version less than 8.2, or a PostgreSQL derivative that does not support returning.

Note that when this method is used, insert will not return the primary key of the inserted row, you will have to get the primary key of the inserted row before inserting via nextval, or after inserting via currval or lastval (making sure to use the same database connection for currval or lastval).

     # File lib/sequel/adapters/shared/postgres.rb
1956 def disable_insert_returning
1957   clone(:disable_insert_returning=>true)
1958 end
empty?() click to toggle source

Always return false when using VALUES

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1961 def empty?
1962   return false if @opts[:values]
1963   super
1964 end
explain(opts=OPTS) click to toggle source

Return the results of an EXPLAIN query. Boolean options:

:analyze

Use the ANALYZE option.

:buffers

Use the BUFFERS option.

:costs

Use the COSTS option.

:generic_plan

Use the GENERIC_PLAN option.

:memory

Use the MEMORY option.

:settings

Use the SETTINGS option.

:summary

Use the SUMMARY option.

:timing

Use the TIMING option.

:verbose

Use the VERBOSE option.

:wal

Use the WAL option.

Non boolean options:

:format

Use the FORMAT option to change the format of the returned value. Values can be :text, :xml, :json, or :yaml.

:serialize

Use the SERIALIZE option to get timing on serialization. Values can be :none, :text, or :binary.

See the PostgreSQL EXPLAIN documentation for an explanation of what each option does.

In most cases, the return value is a single string. However, using the format: :json option can result in the return value being an array containing a hash.

     # File lib/sequel/adapters/shared/postgres.rb
1994 def explain(opts=OPTS)
1995   rows = clone(:append_sql=>explain_sql_string_origin(opts)).map(:'QUERY PLAN')
1996 
1997   if rows.length == 1
1998     rows[0]
1999   elsif rows.all?{|row| String === row}
2000     rows.join("\r\n") 
2001   # :nocov:
2002   else
2003     # This branch is unreachable in tests, but it seems better to just return
2004     # all rows than throw in error if this case actually happens.
2005     rows
2006   # :nocov:
2007   end
2008 end
for_key_share() click to toggle source

Return a cloned dataset which will use FOR KEY SHARE to lock returned rows. Supported on PostgreSQL 9.3+.

     # File lib/sequel/adapters/shared/postgres.rb
2012 def for_key_share
2013   cached_lock_style_dataset(:_for_key_share_ds, :key_share)
2014 end
for_no_key_update() click to toggle source

Return a cloned dataset which will use FOR NO KEY UPDATE to lock returned rows. This is generally a better choice than using for_update on PostgreSQL, unless you will be deleting the row or modifying a key column. Supported on PostgreSQL 9.3+.

     # File lib/sequel/adapters/shared/postgres.rb
2019 def for_no_key_update
2020   cached_lock_style_dataset(:_for_no_key_update_ds, :no_key_update)
2021 end
for_share() click to toggle source

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

     # File lib/sequel/adapters/shared/postgres.rb
2024 def for_share
2025   cached_lock_style_dataset(:_for_share_ds, :share)
2026 end
insert(*values) click to toggle source

Insert given values into the database.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2089 def insert(*values)
2090   if @opts[:returning]
2091     # Already know which columns to return, let the standard code handle it
2092     super
2093   elsif @opts[:sql] || @opts[:disable_insert_returning]
2094     # Raw SQL used or RETURNING disabled, just use the default behavior
2095     # and return nil since sequence is not known.
2096     super
2097     nil
2098   else
2099     # Force the use of RETURNING with the primary key value,
2100     # unless it has been disabled.
2101     returning(insert_pk).insert(*values){|r| return r.values.first}
2102   end
2103 end
insert_conflict(opts=OPTS) click to toggle source

Handle uniqueness violations when inserting, by updating the conflicting row, using ON CONFLICT. With no options, uses ON CONFLICT DO NOTHING. Options:

:conflict_where

The index filter, when using a partial index to determine uniqueness.

:constraint

An explicit constraint name, has precendence over :target.

:target

The column name or expression to handle uniqueness violations on.

:update

A hash of columns and values to set. Uses ON CONFLICT DO UPDATE.

:update_where

A WHERE condition to use for the update.

Examples:

DB[:table].insert_conflict.insert(a: 1, b: 2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT DO NOTHING

DB[:table].insert_conflict(constraint: :table_a_uidx).insert(a: 1, b: 2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT ON CONSTRAINT table_a_uidx DO NOTHING

DB[:table].insert_conflict(target: :a).insert(a: 1, b: 2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT (a) DO NOTHING

DB[:table].insert_conflict(target: :a, conflict_where: {c: true}).insert(a: 1, b: 2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT (a) WHERE (c IS TRUE) DO NOTHING

DB[:table].insert_conflict(target: :a, update: {b: Sequel[:excluded][:b]}).insert(a: 1, b: 2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT (a) DO UPDATE SET b = excluded.b

DB[:table].insert_conflict(constraint: :table_a_uidx,
  update: {b: Sequel[:excluded][:b]}, update_where: {Sequel[:table][:status_id] => 1}).insert(a: 1, b: 2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT ON CONSTRAINT table_a_uidx
# DO UPDATE SET b = excluded.b WHERE (table.status_id = 1)
     # File lib/sequel/adapters/shared/postgres.rb
2140 def insert_conflict(opts=OPTS)
2141   clone(:insert_conflict => opts)
2142 end
insert_ignore() click to toggle source

Ignore uniqueness/exclusion violations when inserting, using ON CONFLICT DO NOTHING. Exists mostly for compatibility to MySQL’s insert_ignore. Example:

DB[:table].insert_ignore.insert(a: 1, b: 2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT DO NOTHING
     # File lib/sequel/adapters/shared/postgres.rb
2150 def insert_ignore
2151   insert_conflict
2152 end
insert_select(*values) click to toggle source

Insert a record, returning the record inserted, using RETURNING. Always returns nil without running an INSERT statement if disable_insert_returning is used. If the query runs but returns no values, returns false.

     # File lib/sequel/adapters/shared/postgres.rb
2157 def insert_select(*values)
2158   return unless supports_insert_select?
2159   # Handle case where query does not return a row
2160   server?(:default).with_sql_first(insert_select_sql(*values)) || false
2161 end
insert_select_sql(*values) click to toggle source

The SQL to use for an insert_select, adds a RETURNING clause to the insert unless the RETURNING clause is already present.

     # File lib/sequel/adapters/shared/postgres.rb
2165 def insert_select_sql(*values)
2166   ds = opts[:returning] ? self : returning
2167   ds.insert_sql(*values)
2168 end
join_table(type, table, expr=nil, options=OPTS, &block) click to toggle source

Support SQL::AliasedExpression as expr to setup a USING join with a table alias for the USING columns.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2172 def join_table(type, table, expr=nil, options=OPTS, &block)
2173   if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all?
2174     options = options.merge(:join_using=>true)
2175   end
2176   super
2177 end
lock(mode, opts=OPTS) { || ... } click to toggle source

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.

     # File lib/sequel/adapters/shared/postgres.rb
2184 def lock(mode, opts=OPTS)
2185   if defined?(yield) # perform locking inside a transaction and yield to block
2186     @db.transaction(opts){lock(mode, opts); yield}
2187   else
2188     sql = 'LOCK TABLE '.dup
2189     source_list_append(sql, @opts[:from])
2190     mode = mode.to_s.upcase.strip
2191     unless LOCK_MODES.include?(mode)
2192       raise Error, "Unsupported lock mode: #{mode}"
2193     end
2194     sql << " IN #{mode} MODE"
2195     @db.execute(sql, opts)
2196   end
2197   nil
2198 end
merge(&block) click to toggle source

Support MERGE RETURNING on PostgreSQL 17+.

     # File lib/sequel/adapters/shared/postgres.rb
2201 def merge(&block)
2202   sql = merge_sql
2203   if uses_returning?(:merge)
2204     returning_fetch_rows(sql, &block)
2205   else
2206     execute_ddl(sql)
2207   end
2208 end
merge_delete_when_not_matched_by_source(&block) click to toggle source

Return a dataset with a WHEN NOT MATCHED BY SOURCE THEN DELETE clause added to the MERGE statement. If a block is passed, treat it as a virtual row and use it as additional conditions for the match.

merge_delete_not_matched_by_source
# WHEN NOT MATCHED BY SOURCE THEN DELETE

merge_delete_not_matched_by_source{a > 30}
# WHEN NOT MATCHED BY SOURCE AND (a > 30) THEN DELETE
     # File lib/sequel/adapters/shared/postgres.rb
2219 def merge_delete_when_not_matched_by_source(&block)
2220   _merge_when(:type=>:delete_not_matched_by_source, &block)
2221 end
merge_do_nothing_when_matched(&block) click to toggle source

Return a dataset with a WHEN MATCHED THEN DO NOTHING clause added to the MERGE statement. If a block is passed, treat it as a virtual row and use it as additional conditions for the match.

merge_do_nothing_when_matched
# WHEN MATCHED THEN DO NOTHING

merge_do_nothing_when_matched{a > 30}
# WHEN MATCHED AND (a > 30) THEN DO NOTHING
     # File lib/sequel/adapters/shared/postgres.rb
2232 def merge_do_nothing_when_matched(&block)
2233   _merge_when(:type=>:matched, &block)
2234 end
merge_do_nothing_when_not_matched(&block) click to toggle source

Return a dataset with a WHEN NOT MATCHED THEN DO NOTHING clause added to the MERGE statement. If a block is passed, treat it as a virtual row and use it as additional conditions for the match.

merge_do_nothing_when_not_matched
# WHEN NOT MATCHED THEN DO NOTHING

merge_do_nothing_when_not_matched{a > 30}
# WHEN NOT MATCHED AND (a > 30) THEN DO NOTHING
     # File lib/sequel/adapters/shared/postgres.rb
2245 def merge_do_nothing_when_not_matched(&block)
2246   _merge_when(:type=>:not_matched, &block)
2247 end
merge_do_nothing_when_not_matched_by_source(&block) click to toggle source

Return a dataset with a WHEN NOT MATCHED BY SOURCE THEN DO NOTHING clause added to the MERGE BY SOURCE statement. If a block is passed, treat it as a virtual row and use it as additional conditions for the match.

merge_do_nothing_when_not_matched_by_source
# WHEN NOT MATCHED BY SOURCE THEN DO NOTHING

merge_do_nothing_when_not_matched_by_source{a > 30}
# WHEN NOT MATCHED BY SOURCE AND (a > 30) THEN DO NOTHING
     # File lib/sequel/adapters/shared/postgres.rb
2258 def merge_do_nothing_when_not_matched_by_source(&block)
2259   _merge_when(:type=>:not_matched_by_source, &block)
2260 end
merge_insert(*values, &block) click to toggle source

Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
2263 def merge_insert(*values, &block)
2264   h = {:type=>:insert, :values=>values}
2265   if @opts[:override]
2266     h[:override] = insert_override_sql(String.new)
2267   end
2268   _merge_when(h, &block)
2269 end
merge_update_when_not_matched_by_source(values, &block) click to toggle source

Return a dataset with a WHEN NOT MATCHED BY SOURCE THEN UPDATE clause added to the MERGE statement. If a block is passed, treat it as a virtual row and use it as additional conditions for the match.

merge_update_not_matched_by_source(i1: Sequel[:i1]+:i2+10, a: Sequel[:a]+:b+20)
# WHEN NOT MATCHED BY SOURCE THEN UPDATE SET i1 = (i1 + i2 + 10), a = (a + b + 20)

merge_update_not_matched_by_source(i1: :i2){a > 30}
# WHEN NOT MATCHED BY SOURCE AND (a > 30) THEN UPDATE SET i1 = i2
     # File lib/sequel/adapters/shared/postgres.rb
2280 def merge_update_when_not_matched_by_source(values, &block)
2281   _merge_when(:type=>:update_not_matched_by_source, :values=>values, &block)
2282 end
overriding_system_value() click to toggle source

Use OVERRIDING USER VALUE for INSERT statements, so that identity columns always use the user supplied value, and an error is not raised for identity columns that are GENERATED ALWAYS.

     # File lib/sequel/adapters/shared/postgres.rb
2287 def overriding_system_value
2288   clone(:override=>:system)
2289 end
overriding_user_value() click to toggle source

Use OVERRIDING USER VALUE for INSERT statements, so that identity columns always use the sequence value instead of the user supplied value.

     # File lib/sequel/adapters/shared/postgres.rb
2293 def overriding_user_value
2294   clone(:override=>:user)
2295 end
supports_cte?(type=:select) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
2297 def supports_cte?(type=:select)
2298   if type == :select
2299     server_version >= 80400
2300   else
2301     server_version >= 90100
2302   end
2303 end
supports_cte_in_subqueries?() click to toggle source

PostgreSQL supports using the WITH clause in subqueries if it supports using WITH at all (i.e. on PostgreSQL 8.4+).

     # File lib/sequel/adapters/shared/postgres.rb
2307 def supports_cte_in_subqueries?
2308   supports_cte?
2309 end
supports_distinct_on?() click to toggle source

DISTINCT ON is a PostgreSQL extension

     # File lib/sequel/adapters/shared/postgres.rb
2312 def supports_distinct_on?
2313   true
2314 end
supports_group_cube?() click to toggle source

PostgreSQL 9.5+ supports GROUP CUBE

     # File lib/sequel/adapters/shared/postgres.rb
2317 def supports_group_cube?
2318   server_version >= 90500
2319 end
supports_group_rollup?() click to toggle source

PostgreSQL 9.5+ supports GROUP ROLLUP

     # File lib/sequel/adapters/shared/postgres.rb
2322 def supports_group_rollup?
2323   server_version >= 90500
2324 end
supports_grouping_sets?() click to toggle source

PostgreSQL 9.5+ supports GROUPING SETS

     # File lib/sequel/adapters/shared/postgres.rb
2327 def supports_grouping_sets?
2328   server_version >= 90500
2329 end
supports_insert_conflict?() click to toggle source

PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
2337 def supports_insert_conflict?
2338   server_version >= 90500
2339 end
supports_insert_select?() click to toggle source

True unless insert returning has been disabled for this dataset.

     # File lib/sequel/adapters/shared/postgres.rb
2332 def supports_insert_select?
2333   !@opts[:disable_insert_returning]
2334 end
supports_lateral_subqueries?() click to toggle source

PostgreSQL 9.3+ supports lateral subqueries

     # File lib/sequel/adapters/shared/postgres.rb
2342 def supports_lateral_subqueries?
2343   server_version >= 90300
2344 end
supports_merge?() click to toggle source

PostgreSQL 15+ supports MERGE.

     # File lib/sequel/adapters/shared/postgres.rb
2352 def supports_merge?
2353   server_version >= 150000
2354 end
supports_modifying_joins?() click to toggle source

PostgreSQL supports modifying joined datasets

     # File lib/sequel/adapters/shared/postgres.rb
2347 def supports_modifying_joins?
2348   true
2349 end
supports_nowait?() click to toggle source

PostgreSQL supports NOWAIT.

     # File lib/sequel/adapters/shared/postgres.rb
2357 def supports_nowait?
2358   true
2359 end
supports_regexp?() click to toggle source

PostgreSQL supports pattern matching via regular expressions

     # File lib/sequel/adapters/shared/postgres.rb
2372 def supports_regexp?
2373   true
2374 end
supports_returning?(type) click to toggle source

MERGE RETURNING is supported on PostgreSQL 17+. Other RETURNING is supported on all supported PostgreSQL versions.

     # File lib/sequel/adapters/shared/postgres.rb
2363 def supports_returning?(type)
2364   if type == :merge
2365     server_version >= 170000
2366   else
2367     true
2368   end
2369 end
supports_skip_locked?() click to toggle source

PostgreSQL 9.5+ supports SKIP LOCKED.

     # File lib/sequel/adapters/shared/postgres.rb
2377 def supports_skip_locked?
2378   server_version >= 90500
2379 end
supports_timestamp_timezones?() click to toggle source

PostgreSQL supports timezones in literal timestamps

     # File lib/sequel/adapters/shared/postgres.rb
2384 def supports_timestamp_timezones?
2385   # SEQUEL6: Remove
2386   true
2387 end
supports_window_clause?() click to toggle source

PostgreSQL 8.4+ supports WINDOW clause.

     # File lib/sequel/adapters/shared/postgres.rb
2391 def supports_window_clause?
2392   server_version >= 80400
2393 end
supports_window_function_frame_option?(option) click to toggle source

Base support added in 8.4, offset supported added in 9.0, GROUPS and EXCLUDE support added in 11.0.

     # File lib/sequel/adapters/shared/postgres.rb
2402 def supports_window_function_frame_option?(option)
2403   case option
2404   when :rows, :range
2405     true
2406   when :offset
2407     server_version >= 90000
2408   when :groups, :exclude
2409     server_version >= 110000
2410   else
2411     false
2412   end
2413 end
supports_window_functions?() click to toggle source

PostgreSQL 8.4+ supports window functions

     # File lib/sequel/adapters/shared/postgres.rb
2396 def supports_window_functions?
2397   server_version >= 80400
2398 end
truncate(opts = OPTS) click to toggle source

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"

DB[:table].truncate(cascade: true, only: true, restart: true)
# TRUNCATE TABLE ONLY "table" RESTART IDENTITY CASCADE
Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2431 def truncate(opts = OPTS)
2432   if opts.empty?
2433     super()
2434   else
2435     clone(:truncate_opts=>opts).truncate
2436   end
2437 end
with_ties() click to toggle source

Use WITH TIES when limiting the result set to also include additional rules that have the same results for the order column as the final row. Requires PostgreSQL 13.

     # File lib/sequel/adapters/shared/postgres.rb
2442 def with_ties
2443   clone(:limit_with_ties=>true)
2444 end

Protected Instance Methods

_import(columns, values, opts=OPTS) click to toggle source

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.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2452 def _import(columns, values, opts=OPTS)
2453   if @opts[:returning]
2454     # no transaction: our multi_insert_sql_strategy should guarantee
2455     # that there's only ever a single statement.
2456     sql = multi_insert_sql(columns, values)[0]
2457     returning_fetch_rows(sql).map{|v| v.length == 1 ? v.values.first : v}
2458   elsif opts[:return] == :primary_key
2459     returning(insert_pk)._import(columns, values, opts)
2460   else
2461     super
2462   end
2463 end
to_prepared_statement(type, *a) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2465 def to_prepared_statement(type, *a)
2466   if type == :insert && !@opts.has_key?(:returning)
2467     returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a)
2468   else
2469     super
2470   end
2471 end

Private Instance Methods

_merge_do_nothing_sql(sql, data) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
2486 def _merge_do_nothing_sql(sql, data)
2487   sql << " THEN DO NOTHING"
2488 end
_merge_insert_sql(sql, data) click to toggle source

Append the INSERT sql used in a MERGE

     # File lib/sequel/adapters/shared/postgres.rb
2476 def _merge_insert_sql(sql, data)
2477   sql << " THEN INSERT"
2478   columns, values = _parse_insert_sql_args(data[:values])
2479   _insert_columns_sql(sql, columns)
2480   if override = data[:override]
2481     sql << override
2482   end
2483   _insert_values_sql(sql, values)
2484 end
_merge_when_sql(sql) click to toggle source

Support MERGE RETURNING on PostgreSQL 17+.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2491 def _merge_when_sql(sql)
2492   super
2493   insert_returning_sql(sql) if uses_returning?(:merge)
2494 end
_truncate_sql(table) click to toggle source

Format TRUNCATE statement with PostgreSQL specific options.

     # File lib/sequel/adapters/shared/postgres.rb
2497 def _truncate_sql(table)
2498   to = @opts[:truncate_opts] || OPTS
2499   "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}"
2500 end
aggreate_dataset_use_from_self?() click to toggle source

Use from_self for aggregate dataset using VALUES.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2503 def aggreate_dataset_use_from_self?
2504   super || @opts[:values]
2505 end
check_truncation_allowed!() click to toggle source

Allow truncation of multiple source tables.

     # File lib/sequel/adapters/shared/postgres.rb
2508 def check_truncation_allowed!
2509   raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group]
2510   raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join]
2511 end
compound_dataset_sql_append(sql, ds) click to toggle source

PostgreSQL requires parentheses around compound datasets if they use CTEs, and using them in other places doesn’t hurt.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2754 def compound_dataset_sql_append(sql, ds)
2755   sql << '('
2756   super
2757   sql << ')'
2758 end
default_timestamp_format() click to toggle source

The strftime format to use when literalizing the time.

     # File lib/sequel/adapters/shared/postgres.rb
2514 def default_timestamp_format
2515   "'%Y-%m-%d %H:%M:%S.%6N%z'"
2516 end
delete_from_sql(sql) click to toggle source

Only include the primary table in the main delete clause

     # File lib/sequel/adapters/shared/postgres.rb
2519 def delete_from_sql(sql)
2520   sql << ' FROM '
2521   source_list_append(sql, @opts[:from][0..0])
2522 end
delete_using_sql(sql) click to toggle source

Use USING to specify additional tables in a delete query

     # File lib/sequel/adapters/shared/postgres.rb
2525 def delete_using_sql(sql)
2526   join_from_sql(:USING, sql)
2527 end
derived_column_list_sql_append(sql, column_aliases) click to toggle source

Handle column aliases containing data types, useful for selecting from functions that return the record data type.

     # File lib/sequel/adapters/shared/postgres.rb
2531 def derived_column_list_sql_append(sql, column_aliases)
2532   c = false
2533   comma = ', '
2534   column_aliases.each do |a|
2535     sql << comma if c
2536     if a.is_a?(Array)
2537       raise Error, "column aliases specified as arrays must have only 2 elements, the first is alias name and the second is data type" unless a.length == 2
2538       a, type = a
2539       identifier_append(sql, a)
2540       sql << " " << db.cast_type_literal(type).to_s
2541     else
2542       identifier_append(sql, a)
2543     end
2544     c ||= true
2545   end
2546 end
explain_sql_string_origin(opts) click to toggle source

A mutable string used as the prefix when explaining a query.

     # File lib/sequel/adapters/shared/postgres.rb
2560 def explain_sql_string_origin(opts)
2561   origin = String.new
2562   origin << 'EXPLAIN '
2563 
2564   # :nocov:
2565   if server_version < 90000
2566     if opts[:analyze]
2567       origin << 'ANALYZE '
2568     end
2569 
2570     return origin
2571   end
2572   # :nocov:
2573 
2574   comma = nil
2575   paren = "("
2576 
2577   add_opt = lambda do |str, value|
2578     origin << paren if paren
2579     origin << comma if comma
2580     origin << str
2581     origin << " FALSE" unless value
2582     comma ||= ', '
2583     paren &&= nil
2584   end
2585 
2586   EXPLAIN_BOOLEAN_OPTIONS.each do |key, str|
2587     unless (value = opts[key]).nil?
2588       add_opt.call(str, value)
2589     end
2590   end
2591 
2592   EXPLAIN_NONBOOLEAN_OPTIONS.each do |key, e_opts|
2593     if value = opts[key]
2594       if str = e_opts[value]
2595         add_opt.call(str, true)
2596       else
2597         raise Sequel::Error, "unrecognized value for Dataset#explain #{key.inspect} option: #{value.inspect}"
2598       end
2599     end
2600   end
2601 
2602   origin << ') ' unless paren
2603   origin
2604 end
full_text_string_join(cols) click to toggle source

Concatenate the expressions with a space in between

     # File lib/sequel/adapters/shared/postgres.rb
2883 def full_text_string_join(cols)
2884   cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')}
2885   cols = cols.zip([' '] * cols.length).flatten
2886   cols.pop
2887   SQL::StringExpression.new(:'||', *cols)
2888 end
insert_conflict_sql(sql) click to toggle source

Add ON CONFLICT clause if it should be used

     # File lib/sequel/adapters/shared/postgres.rb
2607 def insert_conflict_sql(sql)
2608   if opts = @opts[:insert_conflict]
2609     sql << " ON CONFLICT"
2610 
2611     if target = opts[:constraint] 
2612       sql << " ON CONSTRAINT "
2613       identifier_append(sql, target)
2614     elsif target = opts[:target]
2615       sql << ' '
2616       identifier_append(sql, Array(target))
2617       if conflict_where = opts[:conflict_where]
2618         sql << " WHERE "
2619         literal_append(sql, conflict_where)
2620       end
2621     end
2622 
2623     if values = opts[:update]
2624       sql << " DO UPDATE SET "
2625       update_sql_values_hash(sql, values)
2626       if update_where = opts[:update_where]
2627         sql << " WHERE "
2628         literal_append(sql, update_where)
2629       end
2630     else
2631       sql << " DO NOTHING"
2632     end
2633   end
2634 end
insert_into_sql(sql) click to toggle source

Include aliases when inserting into a single table on PostgreSQL 9.5+.

     # File lib/sequel/adapters/shared/postgres.rb
2637 def insert_into_sql(sql)
2638   sql << " INTO "
2639   if (f = @opts[:from]) && f.length == 1
2640     identifier_append(sql, server_version >= 90500 ? f.first : unaliased_identifier(f.first))
2641   else
2642     source_list_append(sql, f)
2643   end
2644 end
insert_override_sql(sql) click to toggle source

Support OVERRIDING SYSTEM|USER VALUE in insert statements

     # File lib/sequel/adapters/shared/postgres.rb
2661 def insert_override_sql(sql)
2662   case opts[:override]
2663   when :system
2664     sql << " OVERRIDING SYSTEM VALUE"
2665   when :user
2666     sql << " OVERRIDING USER VALUE"
2667   end
2668 end
insert_pk() click to toggle source

Return the primary key to use for RETURNING in an INSERT statement

     # File lib/sequel/adapters/shared/postgres.rb
2647 def insert_pk
2648   (f = opts[:from]) && !f.empty? && (t = f.first)
2649 
2650   t = t.call(self) if t.is_a? Sequel::SQL::DelayedEvaluation
2651 
2652   case t
2653   when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier
2654     if pk = db.primary_key(t)
2655       Sequel::SQL::Identifier.new(pk)
2656     end
2657   end
2658 end
join_from_sql(type, sql) click to toggle source

For multiple table support, PostgreSQL requires at least two from tables, with joins allowed.

     # File lib/sequel/adapters/shared/postgres.rb
2672 def join_from_sql(type, sql)
2673   if(from = @opts[:from][1..-1]).empty?
2674     raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join]
2675   else
2676     sql << ' ' << type.to_s << ' '
2677     source_list_append(sql, from)
2678     select_join_sql(sql)
2679   end
2680 end
join_using_clause_using_sql_append(sql, using_columns) click to toggle source

Support table aliases for USING columns

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2683 def join_using_clause_using_sql_append(sql, using_columns)
2684   if using_columns.is_a?(SQL::AliasedExpression)
2685     super(sql, using_columns.expression)
2686     sql << ' AS '
2687     identifier_append(sql, using_columns.alias)
2688   else
2689     super
2690   end
2691 end
literal_blob_append(sql, v) click to toggle source

Use a generic blob quoting method, hopefully overridden in one of the subadapter methods

     # File lib/sequel/adapters/shared/postgres.rb
2694 def literal_blob_append(sql, v)
2695   sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'"
2696 end
literal_false() click to toggle source

PostgreSQL uses FALSE for false values

     # File lib/sequel/adapters/shared/postgres.rb
2699 def literal_false
2700   'false'
2701 end
literal_float(value) click to toggle source

PostgreSQL quotes NaN and Infinity.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2704 def literal_float(value)
2705   if value.finite?
2706     super
2707   elsif value.nan?
2708     "'NaN'"
2709   elsif value.infinite? == 1
2710     "'Infinity'"
2711   else
2712     "'-Infinity'"
2713   end
2714 end
literal_integer(v) click to toggle source

Handle Ruby integers outside PostgreSQL bigint range specially.

     # File lib/sequel/adapters/shared/postgres.rb
2717 def literal_integer(v)
2718   if v > 9223372036854775807 || v < -9223372036854775808
2719     literal_integer_outside_bigint_range(v)
2720   else
2721     v.to_s
2722   end
2723 end
literal_integer_outside_bigint_range(v) click to toggle source

Raise IntegerOutsideBigintRange when attempting to literalize Ruby integer outside PostgreSQL bigint range, so PostgreSQL doesn’t treat the value as numeric.

     # File lib/sequel/adapters/shared/postgres.rb
2728 def literal_integer_outside_bigint_range(v)
2729   raise IntegerOutsideBigintRange, "attempt to literalize Ruby integer outside PostgreSQL bigint range: #{v}"
2730 end
literal_string_append(sql, v) click to toggle source

Assume that SQL standard quoting is on, per Sequel’s defaults

     # File lib/sequel/adapters/shared/postgres.rb
2733 def literal_string_append(sql, v)
2734   sql << "'" << v.gsub("'", "''") << "'"
2735 end
literal_true() click to toggle source

PostgreSQL uses true for true values

     # File lib/sequel/adapters/shared/postgres.rb
2738 def literal_true
2739   'true'
2740 end
multi_insert_sql_strategy() click to toggle source

PostgreSQL supports multiple rows in INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
2743 def multi_insert_sql_strategy
2744   :values
2745 end
non_sql_option?(key) click to toggle source

Dataset options that do not affect the generated SQL.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2748 def non_sql_option?(key)
2749   super || key == :cursor || key == :insert_conflict
2750 end
requires_like_escape?() click to toggle source

Backslash is supported by default as the escape character on PostgreSQL, and using ESCAPE can break LIKE ANY() usage.

     # File lib/sequel/adapters/shared/postgres.rb
2762 def requires_like_escape?
2763   false
2764 end
select_limit_sql(sql) click to toggle source

Support FETCH FIRST WITH TIES on PostgreSQL 13+.

     # File lib/sequel/adapters/shared/postgres.rb
2767 def select_limit_sql(sql)
2768   l = @opts[:limit]
2769   o = @opts[:offset]
2770 
2771   return unless l || o
2772 
2773   if @opts[:limit_with_ties]
2774     if o
2775       sql << " OFFSET "
2776       literal_append(sql, o)
2777     end
2778 
2779     if l
2780       sql << " FETCH FIRST "
2781       literal_append(sql, l)
2782       sql << " ROWS WITH TIES"
2783     end
2784   else
2785     if l
2786       sql << " LIMIT "
2787       literal_append(sql, l)
2788     end
2789 
2790     if o
2791       sql << " OFFSET "
2792       literal_append(sql, o)
2793     end
2794   end
2795 end
select_lock_sql(sql) click to toggle source

Support FOR SHARE locking when using the :share lock style. Use SKIP LOCKED if skipping locked rows.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2799 def select_lock_sql(sql)
2800   lock = @opts[:lock]
2801   case lock
2802   when :share
2803     sql << ' FOR SHARE'
2804   when :no_key_update
2805     sql << ' FOR NO KEY UPDATE'
2806   when :key_share
2807     sql << ' FOR KEY SHARE'
2808   else
2809     super
2810   end
2811 
2812   if lock
2813     if @opts[:skip_locked]
2814       sql << " SKIP LOCKED"
2815     elsif @opts[:nowait]
2816       sql << " NOWAIT"
2817     end
2818   end
2819 end
select_values_sql(sql) click to toggle source

Support VALUES clause instead of the SELECT clause to return rows.

     # File lib/sequel/adapters/shared/postgres.rb
2822 def select_values_sql(sql)
2823   sql << "VALUES "
2824   expression_list_append(sql, opts[:values])
2825 end
select_with_sql_base() click to toggle source

Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2828 def select_with_sql_base
2829   opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
2830 end
select_with_sql_cte(sql, cte) click to toggle source

Support PostgreSQL 14+ CTE SEARCH/CYCLE clauses

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2833 def select_with_sql_cte(sql, cte)
2834   super
2835   select_with_sql_cte_search_cycle(sql, cte)
2836 end
select_with_sql_cte_search_cycle(sql, cte) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
2838 def select_with_sql_cte_search_cycle(sql, cte)
2839   if search_opts = cte[:search]
2840     sql << if search_opts[:type] == :breadth
2841       " SEARCH BREADTH FIRST BY "
2842     else
2843       " SEARCH DEPTH FIRST BY "
2844     end
2845 
2846     identifier_list_append(sql, Array(search_opts[:by]))
2847     sql << " SET "
2848     identifier_append(sql, search_opts[:set] || :ordercol)
2849   end
2850 
2851   if cycle_opts = cte[:cycle]
2852     sql << " CYCLE "
2853     identifier_list_append(sql, Array(cycle_opts[:columns]))
2854     sql << " SET "
2855     identifier_append(sql, cycle_opts[:cycle_column] || :is_cycle)
2856     if cycle_opts.has_key?(:cycle_value)
2857       sql << " TO "
2858       literal_append(sql, cycle_opts[:cycle_value])
2859       sql << " DEFAULT "
2860       literal_append(sql, cycle_opts.fetch(:noncycle_value, false))
2861     end
2862     sql << " USING "
2863     identifier_append(sql, cycle_opts[:path_column] || :path)
2864   end
2865 end
server_version() click to toggle source

The version of the database server

     # File lib/sequel/adapters/shared/postgres.rb
2868 def server_version
2869   db.server_version(@opts[:server])
2870 end
supports_filtered_aggregates?() click to toggle source

PostgreSQL 9.4+ supports the FILTER clause for aggregate functions.

     # File lib/sequel/adapters/shared/postgres.rb
2873 def supports_filtered_aggregates?
2874   server_version >= 90400
2875 end
supports_quoted_function_names?() click to toggle source

PostgreSQL supports quoted function names.

     # File lib/sequel/adapters/shared/postgres.rb
2878 def supports_quoted_function_names?
2879   true
2880 end
update_from_sql(sql) click to toggle source

Use FROM to specify additional tables in an update query

     # File lib/sequel/adapters/shared/postgres.rb
2891 def update_from_sql(sql)
2892   join_from_sql(:FROM, sql)
2893 end
update_table_sql(sql) click to toggle source

Only include the primary table in the main update clause

     # File lib/sequel/adapters/shared/postgres.rb
2896 def update_table_sql(sql)
2897   sql << ' '
2898   source_list_append(sql, @opts[:from][0..0])
2899 end