Substituting composite types and arrays

[bergwerf]

Are there utilities implemented (or planned) to include composite types in queries?

e.g. something like this:

context.query('INSERT INTO table VALUES (@car:row, @cars:array[row]);', {
  'car': ['Volvo', 2005],
  'cars': [
    ['Volvo', 2006],
    ['VW', 2011]
  ]
});

Instread of writing some kind of query/substitution map generator to produce this:

context.query('INSERT INTO table VALUES (ROW(@carBrand, @carYear), ARRAY[ROW[...]...])'...);

Thanks.

[bergwerf]

This is content for a separate issue, but a more severe issue is actually that the only way to read returned arrays and records seems to be using ad hoc string parsing.

[joeconwaystk]

Planned - no, not really. We'll eventually get to every Postgres type, but priority is based on features needed to support other projects. I like the syntax you have proposed here - care to take a crack at implementing it? The database configurations in .travis.yml can be applied to a local database to run the tests.

[bergwerf]

I might have a look. Do you have a good documentation source of the binary PostgreSQL protocol? Earlier you also mentioned this:

1. Add to PostgreSQLDataType enum
2. Add type code and encoding/decoding behavior to PostgreSQLCodec
3. Add data type string mapping to PostgreSQLFormat

I guess those steps are sufficient to support both reading and writing? Also, to support new substitution syntax I might have to change a regex or something somewhere. But my main priority would actually been the thing I mentioned in my second comment (reading returned arrays, e.g. I use an array of integers to store a tree structure).

[joeconwaystk]

It looks more daunting than it actually is: https://www.postgresql.org/docs/9.1/static/protocol.html. Those steps should support both reading and writing. The regex for type may have to be a more inclusive on characters it accepts, as you said. Also note you can omit the type from a substitution variable in a query string and it'll be sent in the text format, instead of binary. The idea is that any unsupported types are allowed, but supported types use the safer and more efficient binary format.