Here's the route from which the query is being executed:
userRouter.route("/new")
.post(function (req, res) {
var user = req.body;
pg.connect(connectionString, function (error, client, done) {
var queryString = "INSERT INTO Users (id, first_name, last_name) VALUES (" + "'" + [user.id, user.first_name, user.last_name].join("','") + "'" + ")";
console.log(queryString);
client.query(queryString, function (error, result) {
console.log(result.rows);
done();
});
});
});
The problem is that the "result" value I'm attempting to reference from within the second console is basically blank:
{
command: 'INSERT',
rowCount: 1,
oid: 0,
rows: [],
fields: [],
_parsers: [],
RowCtor: null,
rowAsArray: false,
_getTypeParser: [Function: bound ]
}
Shouldn't result.rows contain an array containing an object representing the row I just created in the database?
In an INSERT , the data available to RETURNING is the row as it was inserted. This is not so useful in trivial inserts, since it would just repeat the data provided by the client. But it can be very handy when relying on computed default values.
All right, I've figured it out.
Apparently, I was attempting to use a feature that has not yet been implemented in Node-Postgres, as described here: https://github.com/brianc/node-postgres/wiki/Todo
Insert/update/select row count in query result callback
Though this would be extremely awesome off course, it is possible to obtain the behaviour by adding RETURNING id or even RETURNING * to the query. This works fine for single columns for me. I will test this for multiples and the handling of that case by this package. I am quite certain it must be possible in postgres.
So, I updated my query to return all data for the new row:
var queryString = "INSERT INTO Users (first_name, last_name) VALUES (" + "'" + [user.first_name, user.last_name].join("','") + "'" + ") RETURNING *";
And then I modified my query to account for the "row" event handler, which is trigger when a new row is entered into the database. It is within the context of this event handler that the new row data is accessible to me (as per the "RETURNING" parameter above:
var query = client.query(queryString, function (error, result) {
done();
});
query.on("row", function (row, result) {
console.log("Inside the row event handler.");
res.render("users/show", { user: row });
});
Aaaand it works!
update the query and add at the end of the query " RETURNING * "
var queryString = "INSERT INTO Users (id, first_name, last_name) VALUES (" + "'" + [user.id, user.first_name, user.last_name].join("','") + "'" + ") RETURNING *";
get result.rows[0]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With