Last Update: 2021-12-18 12:49:34 -0800

The pg_row_ops extension adds support to Sequel’s DSL to make it easier to deal with PostgreSQL row-valued/composite types.

To load the extension:

Sequel.extension :pg_row_ops

The most common usage is passing an expression to Sequel.pg_row_op:

r = Sequel.pg_row_op(:row_column)

If you have also loaded the pg_row extension, you can use Sequel.pg_row as well:

r = Sequel.pg_row(:row_column)

Also, on most Sequel expression objects, you can call the pg_row method:

r = Sequel[:row_column].pg_row

If you have loaded the core_extensions extension, or you have loaded the core_refinements extension and have activated refinements for the file, you can also use Symbol#pg_row:

r = :row_column.pg_row

There’s only fairly basic support currently. You can use the [] method to access a member of the composite type:

r[:a] # (row_column).a

This can be chained:

r[:a][:b] # ((row_column).a).b

If you’ve loaded the pg_array_ops extension, you there is also support for composite types that include arrays, or arrays of composite types:

r[1][:a] # (row_column[1]).a
r[:a][1] # (row_column).a[1]

The only other support is the splat method:

r.splat # (row_column.*)

The splat method is necessary if you are trying to reference a table’s type when the table has the same name as one of it’s columns. For example:

DB.create_table(:a){Integer :a; Integer :b}

Let’s say you want to reference the composite type for the table:

a = Sequel.pg_row_op(:a)
DB[:a].select(a[:b]) # SELECT (a).b FROM a

Unfortunately, that doesn’t work, as it references the integer column, not the table. The splat method works around this:

DB[:a].select(a.splat[:b]) # SELECT (a.*).b FROM a

Splat also takes an argument which is used for casting. This is necessary if you want to return the composite type itself, instead of the columns in the composite type. For example:

DB[:a].select(a.splat).first # SELECT (a.*) FROM a
# => {:a=>1, :b=>2}

By casting the expression, you can get a composite type returned:

DB[:a].select(a.splat(:a)).first # SELECT (a.*)::a FROM a
# => {:a=>"(1,2)"} # or {:a=>{:a=>1, :b=>2}} if the "a" type has been registered
                   # with the pg_row extension

This feature is mostly useful for a different way to graph tables:

DB[:a].join(:b, id: :b_id).select(Sequel.pg_row_op(:a).splat(:a),
# SELECT (a.*)::a, (b.*)::b FROM a INNER JOIN b ON ( = a.b_id)
# => {:a=>{:id=>1, :b_id=>2}, :b=>{:id=>2}}

Related module: Sequel::Postgres::PGRowOp