Q: Is it possible to handle composite values like `row(123, 'str', true)` ?

[kwatch]

Is it possible to retrieve composite values (ex: row(123, 'str', true, null)) as Ruby's Array object (ex: [123, "str", true, nil]) ?

I hope to execute the following SQL...

select row(emp.*), row(dept.*)    --- !!! composite values !!!
from employees as emp
join departments as dept on emp.dept_id = dept.id

and want to get the following result for example...

[
  [1001, "Alice",    211, "Sales"],
  [1002, "Bob",      215, "Marketing"],
  [1003, "Charlie",  213, "Customer support"],
]

but actually got the following unexpected result:

[
  ["(1001,\"Alice\",211,\"Sales\")"],
  ["(1002,\"Bob\",215,\"Marketing\")"],
  ["(1003,\"Charlie\",215,\"Customer support\")"],
]

If you know how to retrieve composite values correctly, please let me know.

(I found the following page but I can't succeeded to do what I want.) https://deveiate.org/code/pg/PG/TextDecoder/Record.html

[larskanis]

Assuming the following tables:

create table employees (id serial primary key, name text, dept_id int);
create table departments (id serial primary key, name text);
insert into departments (name) VALUES ('Sales'), ('Marketing');
insert into employees (name, dept_id) VALUES ('Alice', 1), ('Bob', 2), ('Charlie', 1);

Then you can simply call:

c = PG.connect
c.exec("select emp.*, dept.* from employees as emp join departments as dept on emp.dept_id = dept.id").values
# => [["1", "Alice", "1", "1", "Sales"], ["2", "Bob", "2", "2", "Marketing"], ["3", "Charlie", "1", "1", "Sales"]] 

You can also receive and decode record types by using a type map with PG::TextDecorder::Record like so:

c.exec("select row(emp.*), row(dept.*) from employees as emp join departments as dept on emp.dept_id = dept.id")
 .map_types!(PG::TypeMapByColumn.new([ PG::TextDecoder::Record.new ]*2)).values
# => [[["1", "Alice", "1"], ["1", "Sales"]], [["2", "Bob", "2"], ["2", "Marketing"]], [["3", "Charlie", "1"], ["1", "Sales"]]] 

It's also possible to directly decode to non-strings like Integer, when using a universal type map as the default for result:

c.type_map_for_results = PG::BasicTypeMapForResults.new(c)
c.exec("select emp.*, dept.* from employees as emp join departments as dept on emp.dept_id = dept.id").values
# => [[1, "Alice", 1, 1, "Sales"], [2, "Bob", 2, 2, "Marketing"], [3, "Charlie", 1, 1, "Sales"]] 

But it can not decode record types properly, since the types of the record entries are not provided by the PostgreSQL server (only the generic record type OID 2249). So these OIDs must be fetched manually like so:

# Fetch a NULL record of our types to retrieve the OIDs of the record entries
eoids = c.exec( "SELECT (NULL::employees).*" )
doids = c.exec( "SELECT (NULL::departments).*" )
# Build type maps (PG::TypeMapByColumn) for decoding both record types
etm = c.type_map_for_results.build_column_map( eoids )
dtm = c.type_map_for_results.build_column_map( doids )
# Now add the type maps to the record query above
c.exec("select row(emp.*), row(dept.*) from employees as emp join departments as dept on emp.dept_id = dept.id")
 .map_types!(PG::TypeMapByColumn.new([
   PG::TextDecoder::Record.new(type_map: etm),
   PG::TextDecoder::Record.new(type_map: dtm)
 ])).values
# => [[[1, "Alice", 1], [1, "Sales"]], [[2, "Bob", 2], [2, "Marketing"]], [[3, "Charlie", 1], [1, "Sales"]]] 

And for completeness it's also possible to not retrieve the record OIDs from the database, but to provide them manually. This returns the same result:

c.exec("select row(emp.*), row(dept.*) from employees as emp join departments as dept on emp.dept_id = dept.id")
 .map_types!(PG::TypeMapByColumn.new([
    PG::TextDecoder::Record.new(type_map: PG::TypeMapByColumn.new([
      # columns of employees
      PG::TextDecoder::Integer.new,
      PG::TextDecoder::String.new,
      PG::TextDecoder::Integer.new,      
    ])),
    PG::TextDecoder::Record.new(type_map: PG::TypeMapByColumn.new([
      # columns of departments
      PG::TextDecoder::Integer.new,
      PG::TextDecoder::String.new,
    ]))
 ])).values
# => [[[1, "Alice", 1], [1, "Sales"]], [[2, "Bob", 2], [2, "Marketing"]], [[3, "Charlie", 1], [1, "Sales"]]] 

And decoding everything automatically can be archived by adding the two composite types of the two tables to your own type registry like so:

# Build a type registry and populate with basic types
btr = PG::BasicTypeRegistry.new.register_default_types
# Build an intermediate type map for generation of the record types
btmfr = PG::BasicTypeMapForResults.new(c)

%w[ employees departments ].each do |type|
  # Fetch a NULL record of our types to retrieve the OIDs of the specific record entries
  oids = c.exec( "SELECT (NULL::#{type}).*" )
  # Build a type map (PG::TypeMapByColumn) for decoding this record type
  dtm = btmfr.build_column_map( oids )
  # Register a new record decoder for decoding our composite type
  btr.register_coder(PG::TextDecoder::Record.new(type_map: dtm, name: type))
end
# Apply our basic type registry to all results retrieved from the server
c.type_map_for_results = PG::BasicTypeMapForResults.new(c, registry: btr)
# Now queries decode both types automatically, if they are returned without wrapping by row()
c.exec("select emp, dept from employees as emp join departments as dept on emp.dept_id = dept.id").values
# => [[[1, "Alice", 1], [1, "Sales"]], [[2, "Bob", 2], [2, "Marketing"]], [[3, "Charlie", 1], [1, "Sales"]]]