Custom type parsers do not run for multi-line statements

[mantariksh]

Minimal reproducible example

See https://github.com/mantariksh/pg-bug-repro/blob/main/pg.mjs, the instructions are in the README.

Behaviour observed

When I pass a custom type parser via the types.getTypeParser function passed to the Client constructor, the type parser runs correctly for single-line statements but not for multi-line statements.

For instance, consider the following table (where enum_Users_roles has two possible values, Admin and View)

CREATE TABLE IF NOT EXISTS "Users" ("id"   SERIAL , "name" VARCHAR(255), "roles" "public"."enum_Users_roles"[], PRIMARY KEY ("id"));

I want Client to check for any values wrapped in braces ({}) and parse them as an array, as follows:

const getTypeParser = () => {
  return (value) => {
    if (
      typeof value === "string" &&
      value.startsWith("{") &&
      value.endsWith("}")
    ) {
      return value.slice(1, -1).split(",");
    }
    return value;
  };
};

const client = new Client({
  connectionString: CONNECTION_STRING,
  types: {
    getTypeParser,
  },
});

When I run a single-line statement, I get the correct result:

const singleStatementResult = await client.query(
  'INSERT INTO "Users" ("id","name","roles") VALUES (DEFAULT,$1,$2) RETURNING "id","name","roles";',
  ["Name", ["Admin", "View"]]
);
console.log(singleStatementResult.rows);
// Console output with array correctly parsed
// [ { id: '1', name: 'Name', roles: [ 'Admin', 'View' ] } ]

However, when I run a multi-line statement, the array is no longer parsed (sorry that the SQL statement here is somewhat complex, I got it directly from sequelize):

const multiStatementResult = await client.query(
    `CREATE OR REPLACE FUNCTION pg_temp.testfunc(OUT response "Users", OUT sequelize_caught_exception text) RETURNS RECORD AS $func_6020ceb2c2d3491c8a77fe107dbabe02$ BEGIN INSERT INTO "Users" ("id","name","roles") VALUES (DEFAULT,'Name',ARRAY['Admin','View']::"enum_Users_roles"[]) RETURNING * INTO response; EXCEPTION WHEN unique_violation THEN GET STACKED DIAGNOSTICS sequelize_caught_exception = PG_EXCEPTION_DETAIL; END $func_6020ceb2c2d3491c8a77fe107dbabe02$ LANGUAGE plpgsql; SELECT (testfunc.response)."id", (testfunc.response)."name", (testfunc.response)."roles", testfunc.sequelize_caught_exception FROM pg_temp.testfunc(); DROP FUNCTION IF EXISTS pg_temp.testfunc();`
);
multiStatementResult.forEach((result) => {
  if (result.rows.length) {
    console.log(result.rows);
  }
});

// Console output, observe array is not parsed
// [
//   {
//     id: 2,
//     name: 'Name',
//     roles: '{Admin,View}',
//     sequelize_caught_exception: null
//   }
// ]

[charmander]

Although this could just be a simplified example, I might as well mention that detecting braces is a bad idea, especially globally in every value processed by a pg client. In real code, if you want to use pg’s types option for this, you should get the array OID for your enum and set a parser specifically for it that always parses it as an array (with or without postgres-array). But maybe you don’t want to use pg’s types option for this – it might be better handled at the SQL layer by casting the enum-typed array to text[], or at a higher layer than pg that knows about the schema.

[mantariksh]

@charmander thanks for responding! This is just a simplified example for the sake of illustrating the bug. I actually encountered this bug as a user of sequelize, which does exactly what you said (get the array OID, set a specific parser), but I traced the bug to this library so thought I'd propose a fix here.

Relevant sequelize code here: https://github.com/sequelize/sequelize/blob/b9e71a7af57568dd85b8a2fdaac59b96ce0f0e95/src/dialects/postgres/connection-manager.js#L81