CODEWITHSUNDEEP
node.jspostgresqlfunction
Pablo
Pablo

Reputation: 90

How to call a postgres 9.6 function with json param in node pg

im stuck with this, thanks for your time, im trying to call a postgres fuction receiving a json array as param, im getting the following error qhen the exec the query:

error: bind message supplies 3 parameters, but prepared statement "" requires 1

im using pg for the connection an query, this is my code

     create() {
    var datajson=[];
    var data=this.params.body;   

    if (Object.keys(data).length>1)
    {
      for (var i=0; i<Object.keys(data).length; i++)
      {  
        datajson.push(data[i]);
        console.log(JSON.stringify(data[i]));
      }
    } 



var _this=this;




pg.connect(Nodal.my.Config.db.main, function(err, client, done) {
  console.log('2');
  if (err) {
      console.log('3');
  }

  console.log("llamoexec"+JSON.stringify(datajson));
  var query = {
    // give the query a unique name
    text: 'SELECT setProduct($1)',
    values:  datajson
  }

  client.query(query, function(err, result) {
      console.log('4');
      if (err) {
          console.log('5'+err);
      }
      done();
  });
});



  }

Thank you so much for your help

Upvotes: 0

Views: 1169

Answers (2)

Pablo
Pablo

Reputation: 90

The final working function is:

  create() {
    var datajson=[];
    var data=this.params.body;   

    if (Object.keys(data).length>=1)
    {
      for (var i=0; i<Object.keys(data).length; i++)
      {  
        datajson.push(data[i]);
        console.log(JSON.stringify(data[i]));
      }
    } 

var _this=this;

pg.connect(Nodal.my.Config.db.main, function(err, client, done) {
  console.log('2');
  if (err) {
      console.log('3');
  }

  console.log("llamoexec"+JSON.stringify(datajson));
  var query = {
    // give the query a unique name
    text: 'SELECT setProduct($1)',
    values:  datajson
  }

  client.query('SELECT setProduct($1)',[JSON.stringify(datajson)], function(err, result) {
      console.log('4');
      if (err) {
          console.log('5'+err.message);
      }
      done();
      _this.respond(result);
  });
});

Upvotes: 0

Reinsbrain
Reinsbrain

Reputation: 2581

I think the problem is in how you are calling client.query. I'm not familiar with the method you are using but this is how I normally use it:

let params = [ "param1", ["param2"] ]; /* array of params */
let query = "select someFunction($1::text, $2::text[])";
client.query( query, params, function (error, result) {
    if (error) { /* handle */ }
    else { /* do work */ }
});

In your case, assuming datajson is intended to be array of strings:

let params = [ datajson ];
let query = "select setProduct($1::text[])";
/* note in your case if you had intended to send in a json string as a string then instead:
let query = select setProduct($1::text);
or if you had intended to send in as json:
let query = select setProduct($1::json);
*/
client.query (query, params, function(error, result){
    if (error) { /* handle it */ }
    else {
        console.log(result);
    }
});

As you can see, params is intended to be an array and referenced in the select string by it's ordinal: $1, $2, etc...

Upvotes: 1

Related Questions