pg_row.rb

The pg_row extension adds support for Sequel to handle PostgreSQL’s row-valued/composite types.

This extension integrates with Sequel’s native postgres and jdbc/postgresql adapters, so that when composite fields are retrieved, they are parsed and returned as instances of Sequel::Postgres::PGRow::(HashRow|ArrayRow), or optionally a custom type. HashRow and ArrayRow are DelegateClasses of Hash and Array, so they mostly act like a hash or array, but not completely (is_a?(Hash) and is_a?(Array) are false). If you want the actual hash for a HashRow, call HashRow#to_hash, and if you want the actual array for an ArrayRow, call ArrayRow#to_a. This is done so that Sequel does not treat a values like an Array or Hash by default, which would cause issues.

In addition to the parsers, this extension comes with literalizers for HashRow and ArrayRow using the standard Sequel literalization callbacks, so they work with on all adapters.

To use this extension, first load it into the Database instance:

DB.extension :pg_row

If you plan to use arrays of composite types, make sure you load the pg_array extension first:

DB.extension :pg_array, :pg_row

You can create an anonymous row type by calling the Sequel.pg_row with an array:

Sequel.pg_row(array)

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 Array#pg_row:

array.pg_row

However, in most cases you are going to want something beyond anonymous row types. This extension allows you to register row types on a per database basis, using Database#register_row_type:

DB.register_row_type(:foo)

When you register the row type, Sequel will query the PostgreSQL system tables to find the related metadata, and will setup a custom HashRow subclass for that type. This includes looking up conversion procs for each column in the type, so that when the composite type is returned from the database, the members of the type have the correct type. Additionally, if the composite type also has an array form, Sequel registers an array type for the composite type, so that array columns of the composite type are converted correctly.

You can then create values of that type by using Database#row_type:

DB.row_type(:address, ['123 Sesame St.', 'Some City', '12345'])

Let’s say table address has columns street, city, and zip. This would return something similar to:

{:street=>'123 Sesame St.', :city=>'Some City', :zip=>'12345'}

You can also use a hash:

DB.row_type(:address, street: '123 Sesame St.', city: 'Some City', zip: '12345')

So if you have a person table that has an address column, here’s how you could insert into the column:

DB[:table].insert(address: DB.row_type(:address, street: '123 Sesame St.', city: 'Some City', zip: '12345'))

Note that registering row types without providing an explicit :converter option creates anonymous classes. This results in ruby being unable to Marshal such objects. You can work around this by assigning the anonymous class to a constant. To get a list of such anonymous classes, you can use the following code:

DB.conversion_procs.select{|k,v| v.is_a?(Sequel::Postgres::PGRow::Parser) && \
  v.converter && (v.converter.name.nil? || v.converter.name == '') }.map{|k,v| v}

See the schema modification guide for details on using row type columns in CREATE/ALTER TABLE statements.

This extension requires both the strscan and delegate libraries.

Related module: Sequel::Postgres::PGRow