string_agg.rb

Path: lib/sequel/extensions/string_agg.rb
Last Update: Sat Jun 02 02:04:22 +0000 2018

frozen-string-literal: true

The string_agg extension adds the ability to perform database-independent aggregate string concatentation. For example, with a table like:

  c1 | c2
  ---+---
  a  | 1
  a  | 2
  a  | 3
  b  | 4

You can return a result set like:

  c1 | c2s
  ---+---
  a  | 1,2,3
  b  | 4

First, you need to load the extension into the database:

  DB.extension :string_agg

Then you can use the Sequel.string_agg method to return a Sequel expression:

  sa = Sequel.string_agg(:column_name)
  # or:
  sa = Sequel.string_agg(:column_name, '-') # custom separator

You can specify the order in which the concatention happens by calling order on the expression:

  sa = Sequel.string_agg(:column_name).order(:other_column)

Additionally, if you want to have the concatenation only operate on distinct values, you can call distinct:

  sa = Sequel.string_agg(:column_name).order(:other_column).distinct

These expressions can be used in your datasets, or anywhere else that Sequel expressions are allowed:

  DB[:table].
    select_group(:c1).
    select_append(Sequel.string_agg(:c2))

This extension currenly supports the following databases:

  • PostgreSQL 9+
  • SQLAnywhere 12+
  • Oracle 11g+ (except distinct)
  • DB2 9.7+ (except distinct)
  • MySQL
  • HSQLDB
  • H2

Related module: Sequel::SQL::StringAgg

[Validate]