|The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 478 pages
RRP £14.95 ($19.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
9.11.1 Handling of Quotation Marks
The code of a PL/pgSQL function is specified in
CREATE FUNCTION as a string literal. If you
write the string literal in the ordinary way with surrounding
single quotes, then any single quotes inside the function body
must be doubled; likewise any backslashes must be doubled (assuming
escape string syntax is used).
Doubling quotes is at best tedious, and in more complicated cases
the code can become downright incomprehensible, because you can
easily find yourself needing half a dozen or more adjacent quote marks.
It's recommended that you instead write the function body as a
“dollar-quoted” string literal (see Volume 1A: 188.8.131.52 Dollar-Quoted String Constants). In the dollar-quoting
approach, you never double any quote marks, but instead take care to
choose a different dollar-quoting delimiter for each level of
nesting you need. For example, you might write the
FUNCTION command as:
CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$ .... $PROC$ LANGUAGE plpgsql;
Within this, you might use quote marks for simple literal strings in
SQL commands and
$$ to delimit fragments of SQL commands
that you are assembling as strings. If you need to quote text that
$$, you could use
$Q$, and so on.
The following chart shows what you have to do when writing quote marks without dollar quoting. It might be useful when translating pre-dollar quoting code into something more comprehensible.
- 1 quotation mark
To begin and end the function body, for example:
CREATE FUNCTION foo() RETURNS integer AS ' .... ' LANGUAGE plpgsql;Anywhere within a single-quoted function body, quote marks must appear in pairs.
- 2 quotation marks
For string literals inside the function body, for example:
a_output := ”Blah”; SELECT * FROM users WHERE f_name=”foobar”;In the dollar-quoting approach, you'd just write:
a_output := 'Blah'; SELECT * FROM users WHERE f_name='foobar';which is exactly what the PL/pgSQL parser would see in either case.
- 4 quotation marks
When you need a single quotation mark in a string constant inside the
function body, for example:
a_output := a_output || ” AND name LIKE ””foobar”” AND xyz”The value actually appended to
AND name LIKE 'foobar' AND xyz. In the dollar-quoting approach, you'd write:
a_output := a_output || $$ AND name LIKE 'foobar' AND xyz$$being careful that any dollar-quote delimiters around this are not just
- 6 quotation marks
When a single quotation mark in a string inside the function body is
adjacent to the end of that string constant, for example:
a_output := a_output || ” AND name LIKE ””foobar”””The value appended to
a_outputwould then be:
AND name LIKE 'foobar'. In the dollar-quoting approach, this becomes:
a_output := a_output || $$ AND name LIKE 'foobar'$$
- 10 quotation marks
When you want two single quotation marks in a string constant (which
accounts for 8 quotation marks) and this is adjacent to the end of that
string constant (2 more). You will probably only need that if
you are writing a function that generates other functions, as in
section 9.12.1 Porting Examples.
a_output := a_output || ” if v_” || referrer_keys.kind || ” like ””””” || referrer_keys.key_string || ””””” then return ””” || referrer_keys.referrer_type || ”””; end if;”;The value of
a_outputwould then be:
if v_... like ”...” then return ”...”; end if;In the dollar-quoting approach, this becomes:
a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$ || referrer_keys.key_string || $$' then return '$$ || referrer_keys.referrer_type || $$'; end if;$$;where we assume we only need to put single quote marks into
a_output, because it will be re-quoted before use.
|ISBN 9781906966065||The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide||See the print edition|