I have a procedure in postgres which completes some validations and inserts some data. If the validations are not passed then it raises an exception.
CREATE OR REPLACE PROCEDURE EnrolStudentToProgram(iProgramID int, iStudentID int, dEnrolDate date, vEnrolDesc varchar(255))
LANGUAGE plpgsql AS
$$
DECLARE
-- Variables
d int;
BEGIN
--validations and inserts
....
IF d = 0 THEN
raise exception
'No assessment seq configured for the requested Program_ID: %', iProgramID;
END IF;
END;
$$;
I call the procedure from a node.js back end using node-postgres pool:
const enrolStudentInProgram = async (req, res) => {
const { studentId, program, enrolDesc } = req.body
if (!studentId || !program || !enrolDesc) {
return res.status(400).json({ message: 'All fields are required' })
}
try {
const enrolled = await pool.query(
`call EnrolStudentToProgram($1,$2,now()::date,$3::varchar(255))`,
[program, studentId, enrolDesc]
)
return res.status(201).json({ message: 'Student Enrolled' })
} catch (err) {
console.error(err)
return res.status(400).json({ message: 'Invalid data received' })
}
}
The procedure functions as desired when executed in pdAdmin with the exception being raised, and everything flows in node.js when there are no exceptions, but if an exception is raised in the procedure I cannot get node.js to recognise this as an error or retrieve the exception message.
How do I cause an error to be recognised and retrieve an error message in Node.js when validations fail?
Update from comments: The console.log of enrolled is below, it is the same whether there is an exception raised or not.
🚀 ~ enrolStudentInProgram ~ enrolled: Result {
command: 'CALL',
rowCount: null,
oid: null,
rows: [],
fields: [],
_parsers: undefined,
_types: TypeOverrides {
_types: {
getTypeParser: [Function: getTypeParser],
setTypeParser: [Function: setTypeParser],
arrayParser: [Object],
builtins: [Object]
},
text: {},
binary: {}
},
RowCtor: null,
rowAsArray: false,
_prebuiltEmptyResultObject: null
}
enrolledthere is nothing in there relating to status or error, and I haven't assigned anything toresyet so nothing in there either.enrolledisundefined, in the case of the exception, ornull, or...what? Is it different when the call succeeds than when it doesn't, is what I'm asking, because that could be helpful info.