Pg-promise: Invoking a function that accepts an array of composite type with a property of integer array (integer[]).

Created on 6 Aug 2018 · 2Comments · Source: vitaly-t/pg-promise

Expected behavior

Not really a bug, but I don't understand how to perform passing of composite value array to a stored procedure that performs batch insert.

Steps to reproduce

ERROR:

["syntax error at or near \"[\" error: syntax error at or near \"[\"\n    at Connection.parseE (D:\\sleek-sign-api\\api\\node_modules\\pg\\lib\\connection.js:545:11)\n    at Connection.parseMessage (D:\\sleek-sign-api\\api\\node_modules\\pg\\lib\\connection.js:370:19)\n    at Socket.<anonymous> (D:\\sleek-sign-api\\api\\node_modules\\pg\\lib\\connection.js:113:22)\n    at Socket.emit (events.js:182:13)\n    at Socket.EventEmitter.emit (domain.js:442:20)\n    at addChunk (_stream_readable.js:283:12)\n    at readableAddChunk (_stream_readable.js:264:11)\n    at Socket.Readable.push (_stream_readable.js:219:10)\n    at TCP.onread (net.js:635:20)"]

Define a composite type:

CREATE TYPE initials_batch_insert_param AS (
    "text" VARCHAR(500),
    "signerName" VARCHAR(500),
    "signerEmail" VARCHAR(500),
    "coordinateX" DOUBLE PRECISION,
    "coordinateY" DOUBLE PRECISION,
    "width" DOUBLE PRECISION,
    "height" DOUBLE PRECISION,
    "pageNumbers" INTEGER[],
    "fontSize" INTEGER,
    "fontFamily" VARCHAR(500),
    "required" BOOLEAN,
    "documentId" INTEGER,
    "userId" INTEGER
);
````

Stored Procedure for performing batch insert

```plpgsql
CREATE OR REPLACE FUNCTION initials_batch_insert
(
  p_initials initials_batch_insert_param[]
)
RETURNS VOID AS $BODY$
BEGIN
  INSERT INTO public."initials"
  (
    "id",
    "text",
    "signerName",
    "signerEmail",
    "coordinateX",
    "coordinateY",
    "width",
    "height",
    "pageNumbers",
    "fontSize",
    "fontFamily",
    "required",
    "createdAt",
    "updatedAt",
    "documentId",
    "userId"
  )
  SELECT   nextval('initials_id_seq')
          ,p."text"
          ,p."signerName"
          ,p."signerEmail"
          ,p."coordinateX"
          ,p."coordinateY"
          ,p."width"
          ,p."height"
          ,p."pageNumbers"
          ,p."fontSize"
          ,p."fontFamily"
          ,p."required"
          ,timezone('utc', now())
          ,timezone('utc', now())
          ,p."documentId"
          ,p."userId"
  FROM UNNEST(p_initials) AS "p";
END; 
$BODY$ LANGUAGE plpgsql;

How I try to call it from pg-promise

const baseCommand = require("../base-command");
const pgp = require('pg-promise');

function CompositeInitialType(data) {
  this.rawType = true; // no escaping, because we return pre-formatted SQL
  this.toPostgres = () => {
    //return pgp.as.format();
    let output = "'{";

    for(let i = 0, len = data.length; i < len; i++) {
      let item = data[i];

      output += `"(`;
      output += `${item.text},`;
      output += `${item.signerName},`;
      output += `${item.signerEmail},`;
      output += `${item.coordinateX},`;
      output += `${item.coordinateY},`;
      output += `${item.width},`;
      output += `${item.height},`;
      output += `${pgp.as.json(item.pageNumbers)},`;
      output += `${item.fontSize},`;
      output += `${item.fontFamily},`;
      output += `${item.required},`;
      output += `${item.documentId},`;
      output += `${item.userId}`;
      output += `)"`;

      if(i !== (len - 1))
        output += ",";
    }

    output += "}'";

    return output;
  }
}

/**
 * @exports insertInitialsCommand
 * 
 * @description Module used for inserting multiple initials.
 * @author Allan A. Chua
 * @version 1.0
 * @since August 06, 2018
 */
module.exports = {
  /**
   * Method used for inserting initials in a batch.
   * 
   * @param {object} params - Parameter object.
   * @param {numeric} params.initials - The ID of the document to store.
   */
  execute: params => {
    let input = params || {};
    let data = [{
      text: "test",
      signerName: "test",
      signerEmail: "[email protected]",
      coordinateX: 132,
      coordinateY: 132,
      width: 200,
      height: 200,
      pageNumbers: [1, 2, 3, 4, 5],
      fontSize: 12,
      fontFamily: 'DawningofaNewDay',
      required: true,
      documentId: input.documentID,
      userId: 1
    }, {
      text: "test 2",
      signerName: "test 2",
      signerEmail: "[email protected]",
      coordinateX: 132,
      coordinateY: 132,
      width: 200,
      height: 200,
      pageNumbers: [1, 2, 3, 4, 5],
      fontSize: 12,
      fontFamily: 'DawningofaNewDay',
      required: true,
      documentId: input.documentID,
      userId: 1
    }];

    // baseCommand.execute invokes pgp.oneOrNone
    return baseCommand.execute("SELECT initials_batch_insert($1::initials_batch_insert_param[])", [new CompositeInitialType(data)]);
  }
};

Environment

Version of pg-promise: ^8.4.2
OS type (Linux/Windows/Mac): Windows
Version of Node.js: 10.5.0

question

Source

allanchua101

All 2 comments

I would guess that perhaps formatting item.pageNumbers as JSON isn't sufficient for tuples?

And you absolutely should use pgp.as.format to format the string, never do manual formatting, i.e. this part:

output += `"(`;
      output += `${item.text},`;
      output += `${item.signerName},`;
      output += `${item.signerEmail},`;
      output += `${item.coordinateX},`;
      output += `${item.coordinateY},`;
      output += `${item.width},`;
      output += `${item.height},`;
      output += `${pgp.as.json(item.pageNumbers)},`;
      output += `${item.fontSize},`;
      output += `${item.fontFamily},`;
      output += `${item.required},`;
      output += `${item.documentId},`;
      output += `${item.userId}`;
      output += `)"`;

should be:

output += pgp.as.format('(${text},${singerName},${coordinateX},${coordinateY},${width},${height},${pageNumbers:json},${fontSize},${fontFamily},${required},${documentId},${userId})', item);

Whether this will be enough or not - I don't know. I would suggest to proceed with this on StackOverflow, as general tuples formatting is something a bit on the outside here.