Pg-promise: Composite types

[kdex]

I have a very simple composite type named poi (Point of Interest):

CREATE TYPE poi AS (
    "id" BIGINT,
    "element" element
);

for which I have defined a constructor as

CREATE OR REPLACE FUNCTION poi (
    BIGINT,
    element
) RETURNS poi AS $$
    SELECT CAST(ROW($1, $2) AS poi);
$$ LANGUAGE 'sql' IMMUTABLE STRICT;

I'd like to use pg-promise to insert an array of pois into a database, for which I'd need to call the constructor for each of them behind the scenes.

A resulting query will look something like:

INSERT INTO "some-table" (location, pois) VALUES (ST_MakePoint(50, 60), ARRAY[poi(2442522, 'node'), poi(359582, 'way')]::poi[]);

I fail to see how pgpDB.helpers.insert could construct such a query, and using template literals with db.query comes with the disadvantage that the query's syntax becomes invalid on the first '.

Neither do I need this to be a transaction, nor do I want to split this up into multiple single INSERTs. Can pg-promise do that?

[vitaly-t]

Do I understand it right that you want to execute just one insert with two column values?

[kdex]

Yes. There's three columns in the original source, but I've simplified the scenario for GitHub.

[vitaly-t]

The tricky part of your query is the formatting for individual parameters, not for the overall query, because you are not doing a multi-row insert, it is just one insert.

Because of that, even though you still can use the helpers namespace for the query itself, it is not of much help.

The main thing that you need is the Custom Type Formatting in this case, which can let you format custom objects in any way you want.

You need one type for the first column, another one for the second one...

[kdex]

Oh, that looks very promising. I'll give it a try. Thanks!

[vitaly-t]

For example, for the first column type you might use:

function STPoint(x, y) {
    this.x = x;
    this.y = y;

    this._rawDBType = true;
    this.formatDBType = function () {
        return pgp.as.format("ST_MakePoint(${x}, ${y})", this);
    };
}

Such a type then can be used directly as a formatting parameter. You might already have a type similar to this, which you then can just extend with _rawDBType and formatDBType.

[vitaly-t]

Accordingly, for the second type you can have this type:

function POI(id, element) {
    this.id = id;
    this.element = element;

    this._rawDBType = true;
    this.formatDBType = function () {
        return pgp.as.format("poin(${id}, ${element})", this);
    };
}

[vitaly-t]

@kdex ignore the previous type examples I gave, use this one instead ;)

complete example:

function STPoint(x, y) {
    this.x = x;
    this.y = y;

    this._rawDBType = true;
    this.formatDBType = function () {
        return pgp.as.format("ST_MakePoint($1, $2)", [this.x, this.y]);
    };
}


function POI(id, element) {
    this.id = id;
    this.element = element;

    this._rawDBType = true;
    this.formatDBType = function () {
        return pgp.as.format("poi($1, $2)", [this.id, this.element]);
    };
}

var loc = new STPoint(50, 60);
var pois = [
    new POI(2442522, 'node'),
    new POI(359582, 'way')
];

db.none("INSERT INTO $1~(location, pois) VALUES($2, $3::poi[])", ['some-table', loc, pois]);

It will execute:

INSERT INTO "some-table"(location, pois) VALUES(ST_MakePoint(50, 60), array[poi(2442522, 'node'),poi(359582, 'way')]::poi[])

[kdex]

Works exactly as advertised! Thanks so much! :smile:

[vitaly-t]

F.Y.I. Important breaking change in v6.5.0.

[vitaly-t]

This part of code would need to be changed for v6.5.0 of the library:

function STPoint(x, y) {
    this.x = x;
    this.y = y;
    this._rawType = true;
    this.toPostgres = a => pgp.as.format('ST_MakePoint($1, $2)', [a.x, a.y])
}

function POI(id, element) {
    this.id = id;
    this.element = element;
    this._rawType = true;
    this.toPostgres = a => pgp.as.format('poi($1, $2)', [a.id, a.element])
}

[kdex]

Thank a lot for saving me the trouble, much obliged! 😃

[vitaly-t]

Actually, you can make even much simpler:

function STPoint(x, y) {
    this._rawType = true;
    this.toPostgres = () => pgp.as.format('ST_MakePoint($1, $2)', [x, y])
}

function POI(id, element) {
    this._rawType = true;
    this.toPostgres = () => pgp.as.format('poi($1, $2)', [id, element])
}

[kdex]

Looking at how one would have to set _rawType to true, I'm actually a little confused: Traditionally, ECMAScript, for the lack of a real private fields, has been using leading underscores to denote that API users aren't supposed to change a field, as it might come with unexpected side effects.

If, at some point, you decide to transform the codebase to a newer ECMAScript version (for which there are plans of supporting true, private fields), users wouldn't be able to change _rawType anymore.

Why is it denoted like a private variable, although based on the advice you're giving, it seems like people are supposed to use it?

[vitaly-t]

_rawType isn't private, and cannot be, because it is accessed by the query-formatting engine, plus it can be set dynamically on an existing object.

The reason it uses an underscore in front is because it is meant to extend existing objects, and the underscore is there to minimize the chances of a conflict with an existing property in the object.

toPostgres is too unique a name, no need for an underscore, but rawType isn't, hence the underscore ;)

[kdex]

Feel free to correct me if I'm wrong, but to me, this sounds like you might benefit from creating a prototype that users are supposed to extend. E.g. something akin to:

class POI extends RawType {
  toPostgres() {
      // …
  }
}

This would even get rid of the need for a _rawType property altogether, as internally, you could rely on x instanceof RawType instead.

(Also feel free to transform this example to ES5 if you don't like syntactical classes.)

[vitaly-t]

This will not work on existing objects. It will only work on types that you declare in your code.

[kdex]

I don't think I'm following you; could you elaborate on that?

[vitaly-t]

With the existing solution you can take any object that was created outside of your own code, and turn it into a self-formatting one, simply by setting the extra properties.

With what you suggest, you would have to wrap each external type into a local class before you can use Custom Type Formatting, that would be very awkward.

[kdex]

Ah, yes, that makes sense. Though wrapping it isn't really that involved:

Object.setPrototypeOf(obj, RawType.prototype);

But before I go as far and recommend you mess with prototypes at runtime, which will hurt performance in most current engines, I'll stop. 😄 Thanks for the explanation!

[vitaly-t]

It is worth noting that syntax for Custom Type Formatting slightly changed in v6.5.0 :wink:

[vitaly-t]

Version 7.2.0 changed it a bit.