pg-promise: Pass a function as parameter to func()

Question

I am using pg-promise to access our postgres database. I want to call a stored proc foo(geom) that accepts a geometry datatype (PostGIS). I only have lats/lngs to start with though, so I want to convert them using postGIS.

This looks like this:

db.func('foo', 'ST_MakePoint(' + location.lat + ', ' + location.lng + ')')
  .then((result) => {
    console.log(bar);
  });

I get an error now, complaining that I have an invalid geometry (the conversion does not happen). I am sure the ST_MakePoint works for the values I have. I guess it interprets it as a string and not as a function call when executed on the db.

How should I pass this parameter to make it work?

Answer 1

I am the author of pg-promise ;)

Unlike regular query formatting with pg-promise, where you specify your formatting template through the formatting variables, you skip that when using methods func and proc, and so they are implied from the type of values.

The most elegant solution is to use the Custom Type Formatting supported by the library, which allows you to override any data type and provide your own formatting.

You can introduce your own class Point like this:

function Point(lat, lng) {
    this.lat = +lat;
    this.lng = +lng;
    this.rawType = true; /* use as raw/unescaped value */
    this.toPostgres = p => {
        return 'ST_MakePoint(' + p.lat + ',' + p.lng + ')';
    };
}

Then you can pass it in as a regular value:

const p = new Point(1, 2);

db.func('foo', [p]).then(...)

Alternatively, you can execute your query directly. Not to overvalue method func, which simply executes SELECT * FROM foo, so you can do:

const p = 'ST_MakePoint(' + lat + ',' + lng + ')';

db.any('SELECT * FROM foo($1:raw)', p).then(...)

:raw - injects the raw/unescaped value.

P.S. In future, rather than guessing what pg-promise executes, try pg-monitor instead, or at least handle event query.

UPDATE: 29/04/2018

Updated the syntax for Custom Type Formatting to adhere to the latest supported by pg-promise.

And the shortest syntax for wrapping up a point:

const point = (lat, lng) => ({
    toPostgres: ()=> pgp.as.format('ST_MakePoint($1, $2)', [lat, lng]),
    rawType: true
});

So you can pass it in simply as:

db.func('foo', point(1, 2))