CODEWITHSUNDEEP
postgresqlnoticeraise
Randall
Randall

Reputation: 3034

What is the 'E' in `RAISE NOTICE E'msg %', mymsg`

What is the 'E' in RAISE NOTICE E'msg %', mymsg?

I've seen it used in various Postgres tutorial pages, even in Postgres documentation itself, but I can't find an actual explanation of what it is or does.

It's usually used with messages that use GET STACKED DIAGNOSTICS, but the message itself only looks to me like a normal
RAISE NOTICE 'foo: %, bar: %',foo_value, bar_value;
type of message where the foo and bar values are simply assigned to the specific diagnostic values in the lines before the RAISE NOTICE.

So, what's magic about the E'...'?

Upvotes: 2

Views: 1004

Answers (1)

Zegarek
Zegarek

Reputation: 26103

From the documentation about what goes after the RAISElevel:

After level if any, you can specify a format string (which must be a simple string literal, not an expression).

Another name for a string literal is a string constant documented here. Those can use C-like backslash escape sequences: doc

PostgreSQL also accepts “escape” string constants, which are an extension to the SQL standard. An escape string constant is specified by writing the letter E (upper or lower case) just before the opening single quote, e.g., E'foo'. (When continuing an escape string constant across lines, write E only before the first opening quote.) Within an escape string, a backslash character (\) begins a C-like backslash escape sequence, in which the combination of backslash and following character(s) represent a special byte value, as shown in Table 4.1.

Table 4.1. Backslash Escape Sequences

Backslash Escape Sequence Interpretation
\b backspace
\f form feed
\n newline
\r carriage return
\t tab
\o, \oo, \ooo (o = 0–7) octal byte value
\xh, \xhh (h = 0–9, A–F) hexadecimal byte value
\uxxxx, \Uxxxxxxxx (x = 0–9, A–F) 16 or 32-bit hexadecimal Unicode character value

'E' in RAISE NOTICE E'msg %', mymsg in your opening example is just syntax noise - not particularly magical, not very useful.

  • If mymsg argument contains newlines, or anything requiring some special action to put it there, it will be inserted in place of % placeholder regardless of whether you use E'...' or not.
  • If mymsg argument contains backslash escape sequences, those won't be interpreted as such.

In the doc you linked it does make sense since the \n sequence is in the literal/constant:

GET DIAGNOSTICS stack = PG_CONTEXT;
RAISE NOTICE E'--- Call Stack ---\n%', stack;

Note that stack variable also holds newlines of its own and those end up being inserted into the message even if there's no E out front.

Cheatsheet:

do $r$ begin raise exception '1\n2%4' , '\n3\n'; end $r$;

ERROR:  1\n2\n3\n4
CONTEXT:  PL/pgSQL function inline_code_block line 1 at RAISE

do $r$ begin raise exception E'1\n2%4' , '\n3\n'; end $r$;

ERROR:  1
2\n3\n4
CONTEXT:  PL/pgSQL function inline_code_block line 1 at RAISE

do $r$ begin raise exception '1\n2%4' , E'\n3\n'; end $r$;

ERROR:  1\n2
3
4
CONTEXT:  PL/pgSQL function inline_code_block line 1 at RAISE

do $r$ begin raise exception E'1\n2%4' , E'\n3\n'; end $r$;

ERROR:  1
2
3
4
CONTEXT:  PL/pgSQL function inline_code_block line 1 at RAISE

Upvotes: 3

Related Questions