Parsing PG Tuples

[calebmer]

Has a way been explored to parse Postgres Tuples into JavaScript objects?

I have a query that must select a tuple (i.e. it can’t be flattened), say in the form of a composite type:

create type custom_type as (
  a int,
  b text
);

When you do something like:

select x::custom_type as x from y;

You get an object in pg that looks like:

{
  "x": "(2,'Hello, world!')"
}

Has a library been developed to parse this format? Is there a way to get this information in a better format from postgres? In my scenario I’ve queried pg_catalog for all the database type information, so I know the type OIDs of the tuple attributes.

Another option to explore is calling to_json or a similar function in the select statement, so:

select to_json(x::custom_type) as x from y;

…but if I do that I’m afraid that what I get back will be incompatible with what pg-types parses. Is there any guarantee that I’ll only get back, say strings, so I can run the pg-types parsers anyway on the returned JSON object?

Thanks!

---

In case I’m falling victim to the XY problem for a more complete picture I’m the author of PostGraphQL and I’m trying to add support for Postgres compound types in general.

[vitaly-t]

These data types do not have any special data type associated with them to indicate a tuple. The driver can only receive them as text strings, without knowing the difference. So it is impossible for the driver to detect and/or thus parse those.

It is a higher-level data type, and only your app knows about it when requesting it. That's why only your own app can parse it.

[calebmer]

@vitaly-t are there any tools/recommendations on parsing? Any documentation? The tough part for me is something like (1, '2, 3', 4) would incorrectly parse under a naïve implementation that splits at ,.

[vitaly-t]

I had another look at it within pg-types. The library is definitely getting it as text. However, the type Id i'm getting is 73804, which is a very high number:

Field {
       name: 'data',
       tableID: 73807,
       columnID: 2,
       dataTypeID: 73804,
       dataTypeSize: -1,
       dataTypeModifier: -1,
       format: 'text' }

@brianc my guess is that high type Id-s represent custom types. It would be great if pg-types could support ranges of type id-s for parsers, so we could say something like typeId > 65535 => my custom type parser, or something like that.

If this is the case then it may be possible to integrate automatic parsing for tuples, although it wouldn't be trivial, because the format itself isn't.

Either way, you would need a proper parser for this. And I don't know of any module that can do it today.

[vitaly-t]

@calebmer

The more I looked at it, the more interesting this seems.

I've just set up a new project for myself to get started - pg-tuple, to try and implement the parser.

My initial tests show this is not going to be very simple, once you start considering the following:

• nested/composite tuple types, including tuples of tuples of different types
• arrays of tuples, including arrays of composite tuples

This may take a while to implement, and it requires a lot of tests, but it is a start.

[calebmer]

Awesome! I've watched the repo, so open issues and stuff and I'll do my best to help 👍

What type information do you think we would need to make this work from Postgres? Or is the current focus turning tuples into JS arrays?

[vitaly-t]

@calebmer

The only thing we can do - take tuples as strings, and convert them either into arrays of string, or an object, via an override. See here I've put some initial ideas I had about it.

At this point I suggest that we take this discussion away, into that project. Feel free opening issues there to discuss things ;)

[charmander]

@brianc, why was this closed?

[brianc]

It is a custom type parsing request. It doesn't seem to be a bug w/ the lib - if you have custom types like tuples in your db the best you can do is get the OID out yourself and write your own type parser for it. Trying to close out some of the old issues.

[FbN]

If someone land here a simple parser that auto load types from Postgres https://www.npmjs.com/package/pg-tuple-types