custom parser for type DOMAIN

[dwelle]

It seems that either pg or postgres is regarding a type domain as if it was a regular type, even though it has its own OID, thus currently I can't write a parser for it.

CREATE DOMAIN custom_id AS bigint;

CREATE TABLE test (
    id custom_id
)

The result object specifies dataTypeID that of bigint insteaad of custom_id:

fields: [
  Field {
    name: 'id',
    tableID: 6937143,
    columnID: 1,
    dataTypeID: 20, // OID of bigint instead of custom_id
    dataTypeSize: 8,
    dataTypeModifier: -1,
    format: 'text'
  }
],

Is there any way around this?

[bendrucker]

Not that I know of, but seems like this would have to be a setting on Postgres if this were fixable at all. Not much pg-types can do here.

[dwelle]

If pg at least passed the tableID & columnID to the parserFn, then I could work with that. As it stands I would need to manually loop over rows, check corresponding Field object and map manually.

[dwelle]

For those who are too lazy to fork and can do with a bit of hackery, here's code that may break at any moment:

const db = require("pg");
const Result = require("pg/lib/result");

const ID_PARSERS = {};

// get OIDs of tables where custom_id is used, and their column-orders in
//  such tables
await db.pool.query(`
    SELECT
        t.typbasetype "dataTypeID",
        c.oid "tableID",
        a.attnum "columnID"
    FROM pg_class c
        INNER JOIN pg_attribute a
            ON a.attrelid = c.oid
        INNER JOIN pg_type t
            ON t.oid = a.atttypid
        WHERE
            t.typname = 'custom_id'
        ORDER BY c.oid DESC
`).then(({ rows }) => {
    const idParser = (value) => {
        // do whatever you want here
        return value;
    };

    rows.forEach(({dataTypeID, tableID, columnID}) => {
        ID_PARSERS[`${dataTypeID}.${tableID}.${columnID}`] = idParser;
    });
});

// https://github.com/brianc/node-postgres/blob/ea6ac2ad2313af57159b10a0292c0c178e8e0923/packages/pg/lib/result.js#L86
Result.prototype.addFields = function (fieldDescriptions) {
    this.fields = fieldDescriptions;
    if (this.fields.length) {
        this._parsers = new Array(fieldDescriptions.length);
    }
    for (var i = 0; i < fieldDescriptions.length; i++) {
        const {dataTypeID, tableID, columnID, format} = fieldDescriptions[i];
        const customParser = ID_PARSERS[`${dataTypeID}.${tableID}.${columnID}`];
        if (customParser) {
            this._parsers[i] = customParser;
        } else if (this._types) {
            this._parsers[i] = this._types.getTypeParser(dataTypeID, format || "text");
        } else {
            this._parsers[i] = db.types.getTypeParser(dataTypeID, format || "text");
        }
    }
};

[dwelle]

@bendrucker btw, there is a way to support this natively: use pg_type.oid instead of pg_type.typbasetype which is what pg seems to be using for domains when it builds dataTypeID for the fieldDescriptions.

https://www.postgresql.org/docs/current/catalog-pg-type.html

[bendrucker]

Nice! Is there a change we should be making to pg to support this better?

[dwelle]

I'm getting lost in the codebase. E.g., I'm not sure where the reader is populated:

https://github.com/brianc/node-postgres/blob/5930e4fa38cb49a86f1e890382e53dcd62a9dd10/packages/pg-protocol/src/parser.ts#L228

In any case, I'm not sure if changing dataTypeID to be pg_type.oid is possible as that would be a breaking change. So my suggestion would be to add an extra property, something like exactDataTypeID, which would be set to pg_type.oid (note sure if you're fetching from pg_type. If you're using pg_attribute table instead, then use pg_type.atttypid which should correspond to the same value).

That said, for that to be useful you'd need to add a new method setExactTypeParser(exactDataTypeID, parserFn). But even if you just exposed it in the fieldDescriptions[] it would go a long way as users could just override Result.prototype.addFields or Result.prototype.getTypeParser to use exactDataTypeID instead of dataTypeID.