Search code examples
node.jspostgresqlstored-procedurespg-promise

pg-promise - Example with procedure

I use pg-promise for several projects. I read this:

Functions and Procedures

In PostgreSQL stored procedures are just functions that usually do not return anything.

Suppose we want to call function findAudit to find audit records by user_id and maximum timestamp. We can make such call as shown below:

db.func('findAudit', [123, new Date()])
  .then(function (data) {
    console.log(data); // printing the data returned
})
  .catch(function (error) {
    console.log(error); // printing the error
});

The documentation provides examples except for this two functions. (Notice : excellent documentation otherwise)

Can someone provide an example for Procedure with pg-promise?

PS: I know what is a stored-procedure in Postgresql but I didn't find example with pg-promise (one who could fail/sucess) ...

Thanks

Solution

  • Since PostgreSQL only supports functions, a procedure is considered to be such a function that either returns no data or a simple response, such as operation summary.

    For that reason pg-promise implements method proc that expects either no data or a single row/object of data.

    const res = await db.proc('procName', [123, new Date()]);
//=> either null or a single object with OUT properties

    UPDATE

    Since PostgreSQL v11 came out, proper stored procedures are supported. And pg-promise has been updated where method proc now supports only the new CALL syntax.