Monday 10 April 2017

How to fix multi row node-postgres insert - Syntax Error at or near $4

I have a multi row node-postgres insert that is giving me issues. It is a parameterized query that uses a Common Table Expression to update a primary table and two tables with foreign keys. The query works with the parameters to the primary table, but, when I try to parameterize the foreign key tables, which are multi row, it throws the syntax error.

First, I have this function that creates that takes an array and returns a string of values.

const buildValues = (id, values) => {
    return values 
        .map(val => "(" + id + ", '" + val + "', " + false + ")")
        .join(", ");
    };

Here is my query:

app.post('/api/saveperson', function (req, res) {
  pg.connect(connectionString, (err, client, done) => {

    const insertPerson = `
    WITH x AS ( INSERT INTO people (title, notes, name)
        VALUES ($1, $2, $3)
        RETURNING personId
    ),
    b AS (
        INSERT INTO sports (personid, name, favorite)
        VALUES $4 
    INSERT INTO instructions (personid, name, favorite)
    VALUES $5; 
    `;

      client.query(insertRecipe, 
            [ req.body.title
            , req.body.notes
            , req.body.name
            , queries.buildValues("SELECT personId FROM x", req.body.sports)
            , queries.buildValues("SELECT personId FROM x", req.body.instructions)
            ]
         )
          .then( () => client.end())
          .catch( err => console.log(err));
  });
  return res.json(req.body);
});



via Matt

No comments:

Post a Comment