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

Question

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

you might need to explicitly type the paramenter in your select statement like: SELECT setProduct($1::int[]) or specifically as JSON: SELECT setProduct($1::json) — Reinsbrain
– Reinsbrain, Commented Oct 3, 2018 at 13:58
the error, i think, is that client.query (the way im accustomed to using it) requires more params like this: client.query(querystr,params,function(err,result){ /* do work */ }); — Reinsbrain
– Reinsbrain, Commented Oct 3, 2018 at 14:01
Thanks, yes it was a param issue, i change a little the function and now everything is working — Pablo
– Pablo, Commented Oct 3, 2018 at 23:24

Reinsbrain · Accepted Answer · 2018-10-03 14:33:03Z

1

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...

edited Oct 3, 2018 at 14:33

answered Oct 3, 2018 at 14:11

Reinsbrain

2,6412 gold badges27 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pablo Over a year ago

Thanks, i do something very similar, i will add the changed function, but is some similar to your answer

Pang · Accepted Answer · 2018-10-04 00:55:45Z

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);
  });
});

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related