Thread: PL/pgSQL RAISE EXCEPTION ignores escape characters even with new E'' string syntax

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 ch=
aracters within the string definition for RAISE EXCEPTION are ignored, unle=
ss 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=20
     RAISE EXCEPTION \'This is an error message.\nThis is a message on a ne=
w line\';
     RETURN;
END '=20
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;


CREATE OR REPLACE FUNCTION test_func_exception2() RETURNS void AS
$BODY$
BEGIN=20
     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=20
     RAISE EXCEPTION E'This is an error message.\nThis is a message on a ne=
w line';
     RETURN;
END $BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;


CREATE OR REPLACE FUNCTION test_func_exception4() RETURNS void AS
$BODY$
DECLARE=20
     smessage text;
BEGIN=20
     smessage :=3D 'This is an error message.\nThis is a message on a new l=
ine';
     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 RAIS=
E 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=

"Donald Fraser" <postgres@kiwi-fraser.net> writes:
> 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 $$).

I think you are confused.  plpgsql has never interpreted \n as a return;
if that's happening, it's in the string literal parser that eats the
function body string.

Looking at the source code, it appears that plpgsql's scanner treats
E'' and '' strings the same, which we probably should change sometime
(though the risks for breaking existing functions, perhaps with
unpleasant security implications, seem high).  But the examples you
give address what happens when the string is read by CREATE FUNCTION,
not what plpgsql does when running the function.

            regards, tom lane