PostgreSQL 8.1.9
According to the release notes, PostgreSQL still handles escape characters in
strings as it has in the past, yet PL/pgSQL functions that use escape
characters within the string definition for RAISE EXCEPTION are ignored, unless
the function is created using the old style quote definition (not $$).
Observe the following four test functions using PL/pgSQL.
CREATE OR REPLACE FUNCTION test_func_exception() RETURNS void AS '
BEGIN
RAISE EXCEPTION \'This is an error message.\nThis is a message on a new
line\';
RETURN;
END '
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
CREATE OR REPLACE FUNCTION test_func_exception2() RETURNS void AS
$BODY$
BEGIN
RAISE EXCEPTION 'This is an error message.\nThis is a message on a new
line';
RETURN;
END $BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
CREATE OR REPLACE FUNCTION test_func_exception3() RETURNS void AS
$BODY$
BEGIN
RAISE EXCEPTION E'This is an error message.\nThis is a message on a new
line';
RETURN;
END $BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
CREATE OR REPLACE FUNCTION test_func_exception4() RETURNS void AS
$BODY$
DECLARE
smessage text;
BEGIN
smessage := 'This is an error message.\nThis is a message on a new line';
RAISE EXCEPTION '%',smessage;
RETURN;
END $BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
Out put from the four test functions are as follows:
1)
select test_func_exception();
ERROR: This is an error message.
This is a message on a new line
2)
select test_func_exception2();
ERROR: This is an error message.nThis is a message on a new line
3)
select test_func_exception3();
ERROR: This is an error message.nThis is a message on a new line
4)
select test_func_exception4();
ERROR: This is an error message.
This is a message on a new line
You will note that even using the new E'' string format syntax for the RAISE
EXCEPTION appears to be broken (test_func_exception3()).
I can't find anything in the documentation that suggests this should be the
observed behaviour.
Regards
Donald Fraser
