Module: Sequel::SQL::Builders

Included in:
Sequel
Defined in:
lib/sequel/sql.rb,
lib/sequel/extensions/pg_row.rb,
lib/sequel/extensions/pg_json.rb,
lib/sequel/extensions/pg_array.rb,
lib/sequel/extensions/pg_range.rb,
lib/sequel/extensions/pg_hstore.rb,
lib/sequel/extensions/pg_row_ops.rb,
lib/sequel/extensions/string_agg.rb,
lib/sequel/extensions/pg_inet_ops.rb,
lib/sequel/extensions/pg_json_ops.rb,
lib/sequel/extensions/pg_array_ops.rb,
lib/sequel/extensions/pg_range_ops.rb,
lib/sequel/extensions/pg_hstore_ops.rb,
lib/sequel/extensions/pg_multirange.rb,
lib/sequel/extensions/date_arithmetic.rb,
lib/sequel/extensions/sqlite_json_ops.rb,
lib/sequel/extensions/is_distinct_from.rb,
lib/sequel/extensions/pg_auto_parameterize.rb

Overview

These methods make it easier to create Sequel expressions without using the core extensions.

Instance Method Summary collapse

Instance Method Details

#as(exp, aliaz, columns = nil) ⇒ Object

Create an SQL::AliasedExpression for the given expression and alias.

Sequel.as(:column, :alias) # "column" AS "alias"
Sequel.as(:column, :alias, [:col_alias1, :col_alias2]) # "column" AS "alias"("col_alias1", "col_alias2")



332
333
334
 
# File 'lib/sequel/sql.rb', line 332

def as(exp, aliaz, columns=nil)
  SQL::AliasedExpression.new(exp, aliaz, columns)
end

#asc(arg, opts = OPTS) ⇒ Object

Order the given argument ascending. Options:

:nulls

Set to :first to use NULLS FIRST (so NULL values are ordered before other values), or :last to use NULLS LAST (so NULL values are ordered after other values).

Sequel.asc(:a) # a ASC
Sequel.asc(:b, nulls: :last) # b ASC NULLS LAST



345
346
347
 
# File 'lib/sequel/sql.rb', line 345

def asc(arg, opts=OPTS)
  SQL::OrderedExpression.new(arg, false, opts)
end

#blob(s) ⇒ Object

Return an SQL::Blob that holds the same data as this string. Blobs provide proper escaping of binary data. If given a blob, returns it directly.



352
353
354
355
356
357
358
 
# File 'lib/sequel/sql.rb', line 352

def blob(s)
  if s.is_a?(SQL::Blob)
    s
  else
    SQL::Blob.new(s)
  end
end

#case(*args) ⇒ Object

Return an SQL::CaseExpression created with the given arguments. The first argument are the WHEN/THEN conditions, specified as an array or a hash. The second argument is the ELSE default value. The third optional argument is the CASE expression.

Sequel.case({a: 1}, 0) # SQL: CASE WHEN a THEN 1 ELSE 0 END
Sequel.case({a: 1}, 0, :b) # SQL: CASE b WHEN a THEN 1 ELSE 0 END
Sequel.case({{a: [2,3]} => 1}, 0) # SQL: CASE WHEN a IN (2, 3) THEN 1 ELSE 0 END
Sequel.case([[{a: [2,3]}, 1]], 0) # SQL: CASE WHEN a IN (2, 3) THEN 1 ELSE 0 END



370
371
372
 
# File 'lib/sequel/sql.rb', line 370

def case(*args)
  SQL::CaseExpression.new(*args)
end

#cast(arg, sql_type) ⇒ Object

Cast the reciever to the given SQL type. You can specify a ruby class as a type, and it is handled similarly to using a database independent type in the schema methods.

Sequel.cast(:a, :integer) # CAST(a AS integer)
Sequel.cast(:a, String) # CAST(a AS varchar(255))



379
380
381
 
# File 'lib/sequel/sql.rb', line 379

def cast(arg, sql_type)
  SQL::Cast.new(arg, sql_type)
end

#cast_numeric(arg, sql_type = nil) ⇒ Object

Cast the reciever to the given SQL type (or the database’s default Integer type if none given), and return the result as a NumericExpression, so you can use the bitwise operators on the result. 

Sequel.cast_numeric(:a) # CAST(a AS integer)
Sequel.cast_numeric(:a, Float) # CAST(a AS double precision)



389
390
391
 
# File 'lib/sequel/sql.rb', line 389

def cast_numeric(arg, sql_type = nil)
  cast(arg, sql_type || Integer).sql_number
end

#cast_string(arg, sql_type = nil) ⇒ Object

Cast the reciever to the given SQL type (or the database’s default String type if none given), and return the result as a StringExpression, so you can use + directly on the result for SQL string concatenation.

Sequel.cast_string(:a) # CAST(a AS varchar(255))
Sequel.cast_string(:a, :text) # CAST(a AS text)



399
400
401
 
# File 'lib/sequel/sql.rb', line 399

def cast_string(arg, sql_type = nil)
  cast(arg, sql_type || String).sql_string
end

#char_length(arg) ⇒ Object

Return an emulated function call for getting the number of characters in the argument:

Sequel.char_length(:a) # char_length(a) -- Most databases
Sequel.char_length(:a) # length(a) -- SQLite



408
409
410
 
# File 'lib/sequel/sql.rb', line 408

def char_length(arg)
  SQL::Function.new!(:char_length, [arg], :emulate=>true)
end

#date_add(expr, interval, opts = OPTS) ⇒ Object

Return a DateAdd expression, adding an interval to the date/timestamp expr. Options:

:cast

Cast to the specified type instead of the default if casting



48
49
50
 
# File 'lib/sequel/extensions/date_arithmetic.rb', line 48

def date_add(expr, interval, opts=OPTS)
  DateAdd.new(expr, interval, opts)
end

#date_sub(expr, interval, opts = OPTS) ⇒ Object

Return a DateAdd expression, adding the negative of the interval to the date/timestamp expr. Options:

:cast

Cast to the specified type instead of the default if casting



56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
 
# File 'lib/sequel/extensions/date_arithmetic.rb', line 56

def date_sub(expr, interval, opts=OPTS)
  if defined?(ActiveSupport::Duration) && interval.is_a?(ActiveSupport::Duration)
    interval = interval.parts
  end
  parts = {}
  interval.each do |k,v|
    case v
    when nil
      # ignore
    when Numeric
      parts[k] = -v
    else
      parts[k] = Sequel::SQL::NumericExpression.new(:*, v, -1)
    end
  end
  DateAdd.new(expr, parts, opts)
end

#deep_qualify(qualifier, expr) ⇒ Object

Do a deep qualification of the argument using the qualifier. This recurses into nested structures.

Sequel.deep_qualify(:table, :column) # "table"."column"
Sequel.deep_qualify(:table, Sequel[:column] + 1) # "table"."column" + 1
Sequel.deep_qualify(:table, Sequel[:a].like('b')) # "table"."a" LIKE 'b' ESCAPE '\'



418
419
420
 
# File 'lib/sequel/sql.rb', line 418

def deep_qualify(qualifier, expr)
  Sequel::Qualifier.new(qualifier).transform(expr)
end

#delay(&block) ⇒ Object

Return a delayed evaluation that uses the passed block. This is used to delay evaluations of the code to runtime. For example, with the following code:

ds = DB[:table].where{column > Time.now}

The filter is fixed to the time that where was called. Unless you are only using the dataset once immediately after creating it, that’s probably not desired. If you just want to set it to the time when the query is sent to the database, you can wrap it in Sequel.delay:

ds = DB[:table].where{column > Sequel.delay{Time.now}}

Note that for dates and timestamps, you are probably better off using Sequel::CURRENT_DATE and Sequel::CURRENT_TIMESTAMP instead of this generic delayed evaluation facility.

Raises:



438
439
440
441
 
# File 'lib/sequel/sql.rb', line 438

def delay(&block)
  raise(Error, "Sequel.delay requires a block") unless block
  SQL::DelayedEvaluation.new(block)
end

#desc(arg, opts = OPTS) ⇒ Object

Order the given argument descending. Options:

:nulls

Set to :first to use NULLS FIRST (so NULL values are ordered before other values), or :last to use NULLS LAST (so NULL values are ordered after other values).

Sequel.desc(:a) # b DESC
Sequel.desc(:b, nulls: :first) # b DESC NULLS FIRST



452
453
454
 
# File 'lib/sequel/sql.rb', line 452

def desc(arg, opts=OPTS)
  SQL::OrderedExpression.new(arg, true, opts)
end

#expr(arg = (no_arg=true), &block) ⇒ Object

Wraps the given object in an appropriate Sequel wrapper. If the given object is already a Sequel object, return it directly. For condition specifiers (hashes and arrays of two pairs), true, and false, return a boolean expressions. For numeric objects, return a numeric expression. For strings, return a string expression. For procs or when the method is passed a block, evaluate it as a virtual row and wrap it appropriately. In all other cases, use a generic wrapper.

This method allows you to construct SQL expressions that are difficult to construct via other methods. For example:

Sequel.expr(1) - :a # SQL: (1 - a)

On the Sequel module, this is aliased as #[], for easier use:

Sequel[1] - :a # SQL: (1 - a)



472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
 
# File 'lib/sequel/sql.rb', line 472

def expr(arg=(no_arg=true), &block)
  if defined?(yield)
    if no_arg
      return expr(block)
    else
      raise Error, 'cannot provide both an argument and a block to Sequel.expr'
    end
  elsif no_arg
    raise Error, 'must provide either an argument or a block to Sequel.expr'
  end

  case arg
  when Symbol
    t, c, a = Sequel.split_symbol(arg)

    arg = if t
      SQL::QualifiedIdentifier.new(t, c)
    else
      SQL::Identifier.new(c)
    end

    if a
      arg = SQL::AliasedExpression.new(arg, a)
    end

    arg
  when SQL::Expression, LiteralString, SQL::Blob
    arg
  when Hash
    SQL::BooleanExpression.from_value_pairs(arg, :AND)
  when Array
    if condition_specifier?(arg)
      SQL::BooleanExpression.from_value_pairs(arg, :AND)
    else
      SQL::Wrapper.new(arg)
    end
  when Numeric
    SQL::NumericExpression.new(:NOOP, arg)
  when String
    SQL::StringExpression.new(:NOOP, arg)
  when TrueClass, FalseClass
    SQL::BooleanExpression.new(:NOOP, arg)
  when Proc
    expr(virtual_row(&arg))
  else
    SQL::Wrapper.new(arg)
  end
end

#extract(datetime_part, exp) ⇒ Object

Extract a datetime_part (e.g. year, month) from the given expression:

Sequel.extract(:year, :date) # extract(year FROM "date")



525
526
527
 
# File 'lib/sequel/sql.rb', line 525

def extract(datetime_part, exp)
  SQL::NumericExpression.new(:extract, datetime_part, exp)
end

#function(name, *args) ⇒ Object

Returns a Sequel::SQL::Function with the function name and the given arguments.

Sequel.function(:now) # SQL: now()
Sequel.function(:substr, :a, 1) # SQL: substr(a, 1)



534
535
536
 
# File 'lib/sequel/sql.rb', line 534

def function(name, *args)
  SQL::Function.new(name, *args)
end

#hstore(v) ⇒ Object

Return a Postgres::HStore proxy for the given hash.



313
314
315
316
317
318
319
320
321
322
323
 
# File 'lib/sequel/extensions/pg_hstore.rb', line 313

def hstore(v)
  case v
  when Postgres::HStore
    v
  when Hash
    Postgres::HStore.new(v)
  else
    # May not be defined unless the pg_hstore_ops extension is used
    hstore_op(v)
  end
end

#hstore_op(v) ⇒ Object

Return the object wrapped in an Postgres::HStoreOp.



380
381
382
383
384
385
386
387
 
# File 'lib/sequel/extensions/pg_hstore_ops.rb', line 380

def hstore_op(v)
  case v
  when Postgres::HStoreOp
    v
  else
    Postgres::HStoreOp.new(v)
  end
end

#identifier(name) ⇒ Object

Return the argument wrapped as an SQL::Identifier.

Sequel.identifier(:a) # "a"



541
542
543
 
# File 'lib/sequel/sql.rb', line 541

def identifier(name)
  SQL::Identifier.new(name)
end

#ilike(*args) ⇒ Object

Create a BooleanExpression case insensitive (if the database supports it) pattern match of the receiver with the given patterns. See SQL::StringExpression.like.

Sequel.ilike(:a, 'A%') # "a" ILIKE 'A%' ESCAPE '\'



578
579
580
 
# File 'lib/sequel/sql.rb', line 578

def ilike(*args)
  SQL::StringExpression.like(*(args << {:case_insensitive=>true}))
end

#is_distinct_from(lhs, rhs) ⇒ Object

Return a IsDistinctFrom expression object, using the IS DISTINCT FROM operator with the given left hand side and right hand side.



37
38
39
 
# File 'lib/sequel/extensions/is_distinct_from.rb', line 37

def is_distinct_from(lhs, rhs)
  BooleanExpression.new(:NOOP, IsDistinctFrom.new(lhs, rhs))
end

#join(args, joiner = nil) ⇒ Object

Return a Sequel::SQL::StringExpression representing an SQL string made up of the concatenation of the given array’s elements. If an argument is passed, it is used in between each element of the array in the SQL concatenation.

Sequel.join([:a]) # SQL: a
Sequel.join([:a, :b]) # SQL: a || b
Sequel.join([:a, 'b']) # SQL: a || 'b'
Sequel.join(['a', :b], ' ') # SQL: 'a' || ' ' || b

Raises:



554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
 
# File 'lib/sequel/sql.rb', line 554

def join(args, joiner=nil)
  raise Error, 'argument to Sequel.join must be an array' unless args.is_a?(Array)
  if joiner
    args = args.zip([joiner]*args.length).flatten
    args.pop
  end

  return SQL::StringExpression.new(:NOOP, '') if args.empty?

  args = args.map do |a|
    case a
    when Symbol, ::Sequel::SQL::Expression, ::Sequel::LiteralString, TrueClass, FalseClass, NilClass
      a
    else
      a.to_s
    end
  end
  SQL::StringExpression.new(:'||', *args)
end

#like(*args) ⇒ Object

Create a SQL::BooleanExpression case sensitive (if the database supports it) pattern match of the receiver with the given patterns. See SQL::StringExpression.like.

Sequel.like(:a, 'A%') # "a" LIKE 'A%' ESCAPE '\'



586
587
588
 
# File 'lib/sequel/sql.rb', line 586

def like(*args)
  SQL::StringExpression.like(*args)
end

#lit(s, *args) ⇒ Object

Converts a string into a Sequel::LiteralString, in order to override string literalization, e.g.:

DB[:items].where(abc: 'def').sql #=>
  "SELECT * FROM items WHERE (abc = 'def')"

DB[:items].where(abc: Sequel.lit('def')).sql #=>
  "SELECT * FROM items WHERE (abc = def)"

You can also provide arguments, to create a Sequel::SQL::PlaceholderLiteralString:

DB[:items].select{|o| o.count(Sequel.lit('DISTINCT ?', :a))}.sql #=>
  "SELECT count(DISTINCT a) FROM items"



603
604
605
606
607
608
609
610
611
612
613
 
# File 'lib/sequel/sql.rb', line 603

def lit(s, *args)
  if args.empty?
    if s.is_a?(LiteralString)
      s
    else
      LiteralString.new(s)
    end
  else
    SQL::PlaceholderLiteralString.new(s, args) 
  end
end

#negate(arg) ⇒ Object

Return a Sequel::SQL::BooleanExpression created from the condition specifier, matching none of the conditions.

Sequel.negate(a: true) # SQL: a IS NOT TRUE
Sequel.negate([[:a, true]]) # SQL: a IS NOT TRUE
Sequel.negate([[:a, 1], [:b, 2]]) # SQL: ((a != 1) AND (b != 2))



621
622
623
624
625
626
627
 
# File 'lib/sequel/sql.rb', line 621

def negate(arg)
  if condition_specifier?(arg)
    SQL::BooleanExpression.from_value_pairs(arg, :AND, true)
  else
    raise Error, 'must pass a conditions specifier to Sequel.negate'
  end
end

#or(arg) ⇒ Object

Return a Sequel::SQL::BooleanExpression created from the condition specifier, matching any of the conditions.

Sequel.or(a: true) # SQL: a IS TRUE
Sequel.or([[:a, true]]) # SQL: a IS TRUE
Sequel.or([[:a, 1], [:b, 2]]) # SQL: ((a = 1) OR (b = 2))



635
636
637
638
639
640
641
 
# File 'lib/sequel/sql.rb', line 635

def or(arg)
  if condition_specifier?(arg)
    SQL::BooleanExpression.from_value_pairs(arg, :OR, false)
  else
    raise Error, 'must pass a conditions specifier to Sequel.or'
  end
end

#pg_array(v, array_type = nil) ⇒ Object

Return a Postgres::PGArray proxy for the given array and database array type.



514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
 
# File 'lib/sequel/extensions/pg_array.rb', line 514

def pg_array(v, array_type=nil)
  case v
  when Postgres::PGArray
    if array_type.nil? || v.array_type == array_type
      v
    else
      Postgres::PGArray.new(v.to_a, array_type)
    end
  when Array
    Postgres::PGArray.new(v, array_type)
  else
    # May not be defined unless the pg_array_ops extension is used
    pg_array_op(v)
  end
end

#pg_array_op(v) ⇒ Object

Return the object wrapped in an Postgres::ArrayOp.



303
304
305
306
307
308
309
310
 
# File 'lib/sequel/extensions/pg_array_ops.rb', line 303

def pg_array_op(v)
  case v
  when Postgres::ArrayOp
    v
  else
    Postgres::ArrayOp.new(v)
  end
end

#pg_inet_op(v) ⇒ Object

Return the expression wrapped in the Postgres::InetOp.



171
172
173
174
175
176
177
178
 
# File 'lib/sequel/extensions/pg_inet_ops.rb', line 171

def pg_inet_op(v)
  case v
  when Postgres::InetOp
    v
  else
    Postgres::InetOp.new(v)
  end
end

#pg_json(v) ⇒ Object

Wrap the array or hash in a Postgres::JSONArray or Postgres::JSONHash. Also handles Postgres::JSONObject and JSONBObjects. For other objects, calls Sequel.pg_json_op (which is defined by the pg_json_ops extension).



522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
 
# File 'lib/sequel/extensions/pg_json.rb', line 522

def pg_json(v)
  case v
  when Postgres::JSONObject
    v
  when Array
    Postgres::JSONArray.new(v)
  when Hash
    Postgres::JSONHash.new(v)
  when Postgres::JSONBObject
    v = v.__getobj__
    Postgres::JSONDatabaseMethods.json_primitive_wrapper(v).new(v)
  else
    Sequel.pg_json_op(v)
  end
end

#pg_json_op(v) ⇒ Object

Return the object wrapped in an Postgres::JSONOp.



1394
1395
1396
1397
1398
1399
1400
1401
 
# File 'lib/sequel/extensions/pg_json_ops.rb', line 1394

def pg_json_op(v)
  case v
  when Postgres::JSONOp
    v
  else
    Postgres::JSONOp.new(v)
  end
end

#pg_json_wrap(v) ⇒ Object

Wraps Ruby array, hash, string, integer, float, true, false, and nil values with the appropriate JSON wrapper. Raises an exception for other types.



541
542
543
544
545
546
547
548
 
# File 'lib/sequel/extensions/pg_json.rb', line 541

def pg_json_wrap(v)
  case v
  when *Postgres::JSON_WRAP_CLASSES
    Postgres::JSONDatabaseMethods.json_primitive_wrapper(v).new(v)
  else
    raise Error, "invalid value passed to Sequel.pg_json_wrap: #{v.inspect}"
  end
end

#pg_jsonb(v) ⇒ Object

Wrap the array or hash in a Postgres::JSONBArray or Postgres::JSONBHash. Also handles Postgres::JSONObject and JSONBObjects. For other objects, calls Sequel.pg_json_op (which is defined by the pg_json_ops extension).



554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
 
# File 'lib/sequel/extensions/pg_json.rb', line 554

def pg_jsonb(v)
  case v
  when Postgres::JSONBObject
    v
  when Array
    Postgres::JSONBArray.new(v)
  when Hash
    Postgres::JSONBHash.new(v)
  when Postgres::JSONObject
    v = v.__getobj__
    Postgres::JSONDatabaseMethods.jsonb_primitive_wrapper(v).new(v)
  else
    Sequel.pg_jsonb_op(v)
  end
end

#pg_jsonb_op(v) ⇒ Object

Return the object wrapped in an Postgres::JSONBOp.



1404
1405
1406
1407
1408
1409
1410
1411
 
# File 'lib/sequel/extensions/pg_json_ops.rb', line 1404

def pg_jsonb_op(v)
  case v
  when Postgres::JSONBOp
    v
  else
    Postgres::JSONBOp.new(v)
  end
end

#pg_jsonb_wrap(v) ⇒ Object

Wraps Ruby array, hash, string, integer, float, true, false, and nil values with the appropriate JSONB wrapper. Raises an exception for other types.



573
574
575
576
577
578
579
580
 
# File 'lib/sequel/extensions/pg_json.rb', line 573

def pg_jsonb_wrap(v)
  case v
  when *Postgres::JSON_WRAP_CLASSES
    Postgres::JSONDatabaseMethods.jsonb_primitive_wrapper(v).new(v)
  else
    raise Error, "invalid value passed to Sequel.pg_jsonb_wrap: #{v.inspect}"
  end
end

#pg_multirange(v, db_type) ⇒ Object

Convert the object to a Postgres::PGMultiRange.



349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
 
# File 'lib/sequel/extensions/pg_multirange.rb', line 349

def pg_multirange(v, db_type)
  case v
  when Postgres::PGMultiRange
    if v.db_type == db_type
      v
    else
      Postgres::PGMultiRange.new(v, db_type)
    end
  when Array
    Postgres::PGMultiRange.new(v, db_type)
  else
    # May not be defined unless the pg_range_ops extension is used
    pg_range_op(v)
  end
end

#pg_range(v, db_type = nil) ⇒ Object

Convert the object to a Postgres::PGRange.



520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
 
# File 'lib/sequel/extensions/pg_range.rb', line 520

def pg_range(v, db_type=nil)
  case v
  when Postgres::PGRange
    if db_type.nil? || v.db_type == db_type
      v
    else
      Postgres::PGRange.new(v.begin, v.end, :exclude_begin=>v.exclude_begin?, :exclude_end=>v.exclude_end?, :db_type=>db_type)
    end
  when Range
    Postgres::PGRange.from_range(v, db_type)
  else
    # May not be defined unless the pg_range_ops extension is used
    pg_range_op(v)
  end
end

#pg_range_op(v) ⇒ Object

Return the expression wrapped in the Postgres::RangeOp.



162
163
164
165
166
167
168
169
 
# File 'lib/sequel/extensions/pg_range_ops.rb', line 162

def pg_range_op(v)
  case v
  when Postgres::RangeOp
    v
  else
    Postgres::RangeOp.new(v)
  end
end

#pg_row(expr) ⇒ Object

Wraps the expr array in an anonymous Postgres::PGRow::ArrayRow instance.



550
551
552
553
554
555
556
557
558
 
# File 'lib/sequel/extensions/pg_row.rb', line 550

def pg_row(expr)
  case expr
  when Array
    Postgres::PGRow::ArrayRow.new(expr)
  else
    # Will only work if pg_row_ops extension is loaded
    pg_row_op(expr)
  end
end

#pg_row_op(expr) ⇒ Object

Return a PGRowOp wrapping the given expression.



189
190
191
 
# File 'lib/sequel/extensions/pg_row_ops.rb', line 189

def pg_row_op(expr)
  Postgres::PGRowOp.wrap(expr)
end

#qualify(qualifier, identifier) ⇒ Object

Create a qualified identifier with the given qualifier and identifier

Sequel.qualify(:table, :column) # "table"."column"
Sequel.qualify(:schema, :table) # "schema"."table"
Sequel.qualify(:table, :column).qualify(:schema) # "schema"."table"."column"



648
649
650
 
# File 'lib/sequel/sql.rb', line 648

def qualify(qualifier, identifier)
  SQL::QualifiedIdentifier.new(qualifier, identifier)
end

#skip_pg_auto_param(v) ⇒ Object

Skip auto parameterization for the given object when building queries.



503
504
505
 
# File 'lib/sequel/extensions/pg_auto_parameterize.rb', line 503

def skip_pg_auto_param(v)
  Postgres::AutoParameterize::SkipAutoParam.new(v)
end

#sqlite_json_op(v) ⇒ Object

Return the object wrapped in an SQLite::JSONOp.



270
271
272
273
274
275
276
277
 
# File 'lib/sequel/extensions/sqlite_json_ops.rb', line 270

def sqlite_json_op(v)
  case v
  when SQLite::JSONOp
    v
  else
    SQLite::JSONOp.new(v)
  end
end

#sqlite_jsonb_op(v) ⇒ Object

Return the object wrapped in an SQLite::JSONBOp.



280
281
282
283
284
285
286
287
 
# File 'lib/sequel/extensions/sqlite_json_ops.rb', line 280

def sqlite_jsonb_op(v)
  case v
  when SQLite::JSONBOp
    v
  else
    SQLite::JSONBOp.new(v)
  end
end

#string_agg(*a) ⇒ Object

Return a StringAgg expression for an aggregate string concatentation.



66
67
68
 
# File 'lib/sequel/extensions/string_agg.rb', line 66

def string_agg(*a)
  StringAgg.new(*a)
end

#subscript(exp, *subs) ⇒ Object

Return an SQL::Subscript with the given arguments, representing an SQL array access.

Sequel.subscript(:array, 1) # array[1]
Sequel.subscript(:array, 1, 2) # array[1, 2]
Sequel.subscript(:array, [1, 2]) # array[1, 2]
Sequel.subscript(:array, 1..2) # array[1:2]
Sequel.subscript(:array, 1...3) # array[1:2]



660
661
662
 
# File 'lib/sequel/sql.rb', line 660

def subscript(exp, *subs)
  SQL::Subscript.new(exp, subs.flatten)
end

#trim(arg) ⇒ Object

Return an emulated function call for trimming a string of spaces from both sides (similar to ruby’s String#strip).

Sequel.trim(:a) # trim(a) -- Most databases
Sequel.trim(:a) # ltrim(rtrim(a)) -- Microsoft SQL Server



669
670
671
 
# File 'lib/sequel/sql.rb', line 669

def trim(arg)
  SQL::Function.new!(:trim, [arg], :emulate=>true)
end

#value_list(arg) ⇒ Object

Return a SQL::ValueList created from the given array. Used if the array contains all two element arrays and you want it treated as an SQL value list (IN predicate) instead of as a conditions specifier (similar to a hash). This is not necessary if you are using this array as a value in a filter, but may be necessary if you are using it as a value with placeholder SQL:

DB[:a].where([:a, :b]=>[[1, 2], [3, 4]]) # SQL: (a, b) IN ((1, 2), (3, 4))
DB[:a].where('(a, b) IN ?', [[1, 2], [3, 4]]) # SQL: (a, b) IN ((1 = 2) AND (3 = 4))
DB[:a].where('(a, b) IN ?', Sequel.value_list([[1, 2], [3, 4]])) # SQL: (a, b) IN ((1, 2), (3, 4))

Raises:



682
683
684
685
 
# File 'lib/sequel/sql.rb', line 682

def value_list(arg)
  raise Error, 'argument to Sequel.value_list must be an array' unless arg.is_a?(Array)
  SQL::ValueList.new(arg)
end