Skip site navigation (1) Skip section navigation (2)

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

From: "Donald Fraser" <postgres(at)kiwi-fraser(dot)net>
To: "[BUGS]" <pgsql-bugs(at)postgresql(dot)org>
Subject: PL/pgSQL RAISE EXCEPTION ignores escape characters even with new E'' string syntax
Date: 2007-06-26 17:56:41
Message-ID: 002401c7b81b$59fdedf0$7d64a8c0@demolish1 (view raw or flat)
Thread:
Lists: pgsql-bugs
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

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2007-06-26 19:21:19
Subject: Re: PL/pgSQL RAISE EXCEPTION ignores escape characters even with new E'' string syntax
Previous:From: Tom LaneDate: 2007-06-26 14:19:01
Subject: Re: REVOKE CREATE does not work on default tablespace

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group