0

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
}
6
  • What happens in the node handler when you have a postgres error? Does it return some status code other than 201 that you could then throw? Commented Feb 13, 2024 at 13:34
  • @MattMorgan I'm not 100% sure what you mean by node handler. If I console log enrolled there is nothing in there relating to status or error, and I haven't assigned anything to res yet so nothing in there either. Commented Feb 13, 2024 at 21:03
  • So, enrolled is undefined, in the case of the exception, or null, 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. Commented Feb 13, 2024 at 21:05
  • 1
    @MattMorgan, sorry, understand now. No, there is no different in the output whether there is an exception or not. Detail added to the question. Commented Feb 13, 2024 at 21:31
  • Try this: stackoverflow.com/questions/29972715/… Commented Feb 13, 2024 at 22:30

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.