Module | Sequel::SQL::Builders
In: | lib/sequel/sql.rb lib/sequel/extensions/pg_row.rb lib/sequel/extensions/pg_hstore_ops.rb lib/sequel/extensions/pg_array.rb lib/sequel/extensions/pg_json.rb lib/sequel/extensions/pg_array_ops.rb lib/sequel/extensions/pg_range.rb lib/sequel/extensions/pg_range_ops.rb lib/sequel/extensions/pg_row_ops.rb lib/sequel/extensions/pg_hstore.rb

These methods are designed as replacements for the core extensions, so that Sequel is still easy to use if the core extensions are not enabled.

### Methods

as asc blob case cast cast_numeric cast_string desc expr extract function hstore hstore_op identifier ilike join like lit negate or pg_array pg_array_op pg_json pg_range pg_range_op pg_row pg_row_op qualify subscript value_list

### Public Instance methods

as(exp, aliaz)

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

      Sequel.as(:column, :alias) # "column" AS "alias"

[Source]

         # File lib/sequel/sql.rb, line 291
    291:       def as(exp, aliaz)
    292:         SQL::AliasedExpression.new(exp, aliaz)
    293:       end

asc(arg, opts={})

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

[Source]

         # File lib/sequel/sql.rb, line 304
    304:       def asc(arg, opts={})
    305:         SQL::OrderedExpression.new(arg, false, opts)
    306:       end

blob(s)

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.

[Source]

         # File lib/sequel/sql.rb, line 311
    311:       def blob(s)
    312:         if s.is_a?(SQL::Blob)
    313:           s
    314:         else
    315:           SQL::Blob.new(s)
    316:         end
    317:       end

case(*args)

Return an `SQL::CaseExpression` created with the given arguments.

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

[Source]

         # File lib/sequel/sql.rb, line 323
    323:       def case(*args) # core_sql ignore
    324:         SQL::CaseExpression.new(*args)
    325:       end

cast(arg, sql_type)

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))

[Source]

         # File lib/sequel/sql.rb, line 332
    332:       def cast(arg, sql_type)
    333:         SQL::Cast.new(arg, sql_type)
    334:       end

cast_numeric(arg, sql_type = nil)

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)

[Source]

         # File lib/sequel/sql.rb, line 342
    342:       def cast_numeric(arg, sql_type = nil)
    343:         cast(arg, sql_type || Integer).sql_number
    344:       end

cast_string(arg, sql_type = nil)

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)

[Source]

         # File lib/sequel/sql.rb, line 352
    352:       def cast_string(arg, sql_type = nil)
    353:         cast(arg, sql_type || String).sql_string
    354:       end

desc(arg, opts={})

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

[Source]

         # File lib/sequel/sql.rb, line 365
    365:       def desc(arg, opts={})
    366:         SQL::OrderedExpression.new(arg, true, opts)
    367:       end

expr(arg=(no_arg=true), &block)

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)

[Source]

         # File lib/sequel/sql.rb, line 381
    381:       def expr(arg=(no_arg=true), &block)
    382:         if block_given?
    383:           if no_arg
    384:             return expr(block)
    385:           else
    386:             raise Error, 'cannot provide both an argument and a block to Sequel.expr'
    387:           end
    388:         elsif no_arg
    389:           raise Error, 'must provide either an argument or a block to Sequel.expr'
    390:         end
    391:
    392:         case arg
    393:         when Symbol
    394:           t, c, a = Sequel.split_symbol(arg)
    395:
    396:           arg = if t
    397:             SQL::QualifiedIdentifier.new(t, c)
    398:           else
    399:             SQL::Identifier.new(c)
    400:           end
    401:
    402:           if a
    403:             arg = SQL::AliasedExpression.new(arg, a)
    404:           end
    405:
    406:           arg
    407:         when SQL::Expression, LiteralString, SQL::Blob
    408:           arg
    409:         when Hash
    410:           SQL::BooleanExpression.from_value_pairs(arg, :AND)
    411:         when Array
    412:           if condition_specifier?(arg)
    413:             SQL::BooleanExpression.from_value_pairs(arg, :AND)
    414:           else
    415:             SQL::Wrapper.new(arg)
    416:           end
    417:         when Numeric
    418:           SQL::NumericExpression.new(:NOOP, arg)
    419:         when String
    420:           SQL::StringExpression.new(:NOOP, arg)
    421:         when TrueClass, FalseClass
    422:           SQL::BooleanExpression.new(:NOOP, arg)
    423:         when Proc
    424:           expr(virtual_row(&arg))
    425:         else
    426:           SQL::Wrapper.new(arg)
    427:         end
    428:       end

extract(datetime_part, exp)

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

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

[Source]

         # File lib/sequel/sql.rb, line 434
    434:       def extract(datetime_part, exp)
    435:         SQL::NumericExpression.new(:extract, datetime_part, exp)
    436:       end

function(name, *args)

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)

[Source]

         # File lib/sequel/sql.rb, line 443
    443:       def function(name, *args)
    444:         SQL::Function.new(name, *args)
    445:       end

hstore(v)

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

[Source]

         # File lib/sequel/extensions/pg_hstore.rb, line 285
    285:     def hstore(v)
    286:       case v
    287:       when Postgres::HStore
    288:         v
    289:       when Hash
    290:         Postgres::HStore.new(v)
    291:       else
    292:         # May not be defined unless the pg_hstore_ops extension is used
    293:         hstore_op(v)
    294:       end
    295:     end

hstore_op(v)

Return the object wrapped in an Postgres::HStoreOp.

[Source]

         # File lib/sequel/extensions/pg_hstore_ops.rb, line 270
    270:     def hstore_op(v)
    271:       case v
    272:       when Postgres::HStoreOp
    273:         v
    274:       else
    275:         Postgres::HStoreOp.new(v)
    276:       end
    277:     end

identifier(name)

Return the argument wrapped as an `SQL::Identifier`.

      Sequel.identifier(:a__b) # "a__b"

[Source]

         # File lib/sequel/sql.rb, line 450
    450:       def identifier(name)
    451:         SQL::Identifier.new(name)
    452:       end

ilike(*args)

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%'

[Source]

         # File lib/sequel/sql.rb, line 487
    487:       def ilike(*args)
    488:         SQL::StringExpression.like(*(args << {:case_insensitive=>true}))
    489:       end

join(args, joiner=nil)

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

[Source]

         # File lib/sequel/sql.rb, line 463
    463:       def join(args, joiner=nil)
    464:         raise Error, 'argument to Sequel.join must be an array' unless args.is_a?(Array)
    465:         if joiner
    466:           args = args.zip([joiner]*args.length).flatten
    467:           args.pop
    468:         end
    469:
    470:         return SQL::StringExpression.new(:NOOP, '') if args.empty?
    471:
    472:         args = args.map do |a|
    473:           case a
    474:           when Symbol, ::Sequel::SQL::Expression, ::Sequel::LiteralString, TrueClass, FalseClass, NilClass
    475:             a
    476:           else
    477:             a.to_s
    478:           end
    479:         end
    480:         SQL::StringExpression.new('||''||', *args)
    481:       end

like(*args)

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%'

[Source]

         # File lib/sequel/sql.rb, line 495
    495:       def like(*args)
    496:         SQL::StringExpression.like(*args)
    497:       end

lit(s, *args)

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

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

      DB[:items].filter(: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"

[Source]

         # File lib/sequel/sql.rb, line 512
    512:       def lit(s, *args) # core_sql ignore
    513:         if args.empty?
    514:           if s.is_a?(LiteralString)
    515:             s
    516:           else
    517:             LiteralString.new(s)
    518:           end
    519:         else
    520:           SQL::PlaceholderLiteralString.new(s, args)
    521:         end
    522:       end

negate(arg)

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))

[Source]

         # File lib/sequel/sql.rb, line 530
    530:       def negate(arg)
    531:         if condition_specifier?(arg)
    532:           SQL::BooleanExpression.from_value_pairs(arg, :AND, true)
    533:         else
    534:           raise Error, 'must pass a conditions specifier to Sequel.negate'
    535:         end
    536:       end

or(arg)

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))

[Source]

         # File lib/sequel/sql.rb, line 544
    544:       def or(arg)
    545:         if condition_specifier?(arg)
    546:           SQL::BooleanExpression.from_value_pairs(arg, :OR, false)
    547:         else
    548:           raise Error, 'must pass a conditions specifier to Sequel.or'
    549:         end
    550:       end

pg_array(v, array_type=nil)

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

[Source]

         # File lib/sequel/extensions/pg_array.rb, line 507
    507:     def pg_array(v, array_type=nil)
    508:       case v
    509:       when Postgres::PGArray
    510:         if array_type.nil? || v.array_type == array_type
    511:           v
    512:         else
    513:           Postgres::PGArray.new(v.to_a, array_type)
    514:         end
    515:       when Array
    516:         Postgres::PGArray.new(v, array_type)
    517:       else
    518:         # May not be defined unless the pg_array_ops extension is used
    519:         pg_array_op(v)
    520:       end
    521:     end

pg_array_op(v)

Return the object wrapped in an Postgres::ArrayOp.

[Source]

         # File lib/sequel/extensions/pg_array_ops.rb, line 230
    230:     def pg_array_op(v)
    231:       case v
    232:       when Postgres::ArrayOp
    233:         v
    234:       else
    235:         Postgres::ArrayOp.new(v)
    236:       end
    237:     end

pg_json(v)

Wrap the array or hash in a Postgres::JSONArray or Postgres::JSONHash.

[Source]

         # File lib/sequel/extensions/pg_json.rb, line 173
    173:     def pg_json(v)
    174:       case v
    175:       when Postgres::JSONArray, Postgres::JSONHash
    176:         v
    177:       when Array
    178:         Postgres::JSONArray.new(v)
    179:       when Hash
    180:         Postgres::JSONHash.new(v)
    181:       else
    182:         raise Error, "Sequel.pg_json requires a hash or array argument"
    183:       end
    184:     end

pg_range(v, db_type=nil)

Convert the object to a Postgres::PGRange.

[Source]

         # File lib/sequel/extensions/pg_range.rb, line 484
    484:     def pg_range(v, db_type=nil)
    485:       case v
    486:       when Postgres::PGRange
    487:         if db_type.nil? || v.db_type == db_type
    488:           v
    489:         else
    490:           Postgres::PGRange.new(v.begin, v.end, :exclude_begin=>v.exclude_begin?, :exclude_end=>v.exclude_end?, :db_type=>db_type)
    491:         end
    492:       when Range
    493:         Postgres::PGRange.from_range(v, db_type)
    494:       else
    495:         # May not be defined unless the pg_range_ops extension is used
    496:         pg_range_op(v)
    497:       end
    498:     end

pg_range_op(v)

Return the expression wrapped in the Postgres::RangeOp.

[Source]

         # File lib/sequel/extensions/pg_range_ops.rb, line 127
    127:     def pg_range_op(v)
    128:       case v
    129:       when Postgres::RangeOp
    130:         v
    131:       else
    132:         Postgres::RangeOp.new(v)
    133:       end
    134:     end

pg_row(expr)

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

[Source]

         # File lib/sequel/extensions/pg_row.rb, line 551
    551:     def pg_row(expr)
    552:       case expr
    553:       when Array
    554:         Postgres::PGRow::ArrayRow.new(expr)
    555:       else
    556:         # Will only work if pg_row_ops extension is loaded
    557:         pg_row_op(expr)
    558:       end
    559:     end

pg_row_op(expr)

Return a PGRowOp wrapping the given expression.

[Source]

         # File lib/sequel/extensions/pg_row_ops.rb, line 146
    146:     def pg_row_op(expr)
    147:       Postgres::PGRowOp.wrap(expr)
    148:     end

qualify(qualifier, identifier)

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"

[Source]

         # File lib/sequel/sql.rb, line 557
    557:       def qualify(qualifier, identifier)
    558:         SQL::QualifiedIdentifier.new(qualifier, identifier)
    559:       end

subscript(exp, *subs)

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]

[Source]

         # File lib/sequel/sql.rb, line 567
    567:       def subscript(exp, *subs)
    568:         SQL::Subscript.new(exp, subs.flatten)
    569:       end

value_list(arg)

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].filter([:a, :b]=>[[1, 2], [3, 4]]) # SQL: (a, b) IN ((1, 2), (3, 4))
      DB[:a].filter('(a, b) IN ?', [[1, 2], [3, 4]]) # SQL: (a, b) IN ((1 = 2) AND (3 = 4))
      DB[:a].filter('(a, b) IN ?', Sequel.value_list([[1, 2], [3, 4]])) # SQL: (a, b) IN ((1, 2), (3, 4))

[Source]

         # File lib/sequel/sql.rb, line 580
    580:       def value_list(arg)
    581:         raise Error, 'argument to Sequel.value_list must be an array' unless arg.is_a?(Array)
    582:         SQL::ValueList.new(arg)
    583:       end

[Validate]