I am trying to move a row from one table to another.
The problem is that if I put both queries together, I get "error: cannot insert multiple commands into a prepared statement". What can I do?
exports.deletePost = function(id) {
return db.query(`INSERT INTO deletedjobs
SELECT *
FROM jobs
WHERE id = $1;
DELETE FROM jobs WHERE id = $1;`, [id]).then(results => {
console.log("succesfull transfer");
return results.rows[0];
});
};
EDIT: Following Docs v7.0.0, I found out db.multi can execute a multi-query string, you can try this:
db.multi(`INSERT INTO deletedjobs
SELECT *
FROM jobs
WHERE id = $1;DELETE FROM jobs WHERE id = $1`, [id])
Other way I think the better solution is that you should wrap the query into a function for insert-delete at same time, like below:
CREATE FUNCTION moveJob(id character varying) RETURNs void AS
$BODY$
BEGIN
INSERT INTO deletedjobs
SELECT *
FROM jobs
WHERE id = id;
DELETE FROM jobs WHERE id = id;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;
And call it as postgresql function in your js:
db.any('select moveJob($1)', [id]);
You can also use a WITH ... AS clause (https://www.postgresql.org/docs/9.1/queries-with.html) to execute both queries in one go. So it could look something like that:
exports.deletePost = function(id) {
return db.query(`
WITH
A AS (SELECT * FROM jobs WHERE id = $1),
B AS (INSERT INTO deletedjobs FROM A),
DELETE FROM jobs WHERE id IN (SELECT id FROM A);
`, [id]).then(results => {
console.log("succesfull transfer");
return results.rows[0];
});
};
AND clause, try semicolon ;.select * you should enter columns name, because in later
times you may add a new column, your function does not execute.
