In a PSM I am trying to set the message text for exceptions. This works when I use a simple string constant, e.g.
SIGNAL SQLSTATE '12345' SET MESSAGE_TEXT='test1';
'test1' is successfully used as the mesage of the SQLException I receive.
However if I want to elaborate this with specific information on the error using either a local variable or string concatentation, it doesn't work, e.g.
SIGNAL SQLSTATE '12345' SET MESSAGE_TEXT='test1'||'test2'; [unexpected token: || required: ;]
SIGNAL SQLSTATE '12345' SET MESSAGE_TEXT=CONCAT('test1', 'test2'); [unexpected token: CONCAT]
There is a similar error when using local variables in the PSM.