|The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 454 pages
RRP £14.95 ($19.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
18.104.22.168 Dollar-Quoted String Constants
While the standard syntax for specifying string constants is usually
convenient, it can be difficult to understand when the desired string
contains many single quotes or backslashes, since each of those must
be doubled. To allow more readable queries in such situations,
PostgreSQL provides another way, called
“dollar quoting”, to write string constants.
A dollar-quoted string constant
consists of a dollar sign (
$), an optional
“tag” of zero or more characters, another dollar
sign, an arbitrary sequence of characters that makes up the
string content, a dollar sign, the same tag that began this
dollar quote, and a dollar sign. For example, here are two
different ways to specify the string “Dianne's horse”
using dollar quoting:
$$Dianne's horse$$ $SomeTag$Dianne's horse$SomeTag$
Notice that inside the dollar-quoted string, single quotes can be used without needing to be escaped. Indeed, no characters inside a dollar-quoted string are ever escaped: the string content is always written literally. Backslashes are not special, and neither are dollar signs, unless they are part of a sequence matching the opening tag.
It is possible to nest dollar-quoted string constants by choosing different tags at each nesting level. This is most commonly used in writing function definitions. For example:
$function$ BEGIN RETURN ($1 ~ $q$[\t\r\n\v\\]$q$); END; $function$
Here, the sequence
$q$[\t\r\n\v\\]$q$ represents a
dollar-quoted literal string
[\t\r\n\v\\], which will
be recognized when the function body is executed by
PostgreSQL. But since the sequence does not match
the outer dollar quoting delimiter
$function$, it is
just some more characters within the constant so far as the outer
string is concerned.
The tag, if any, of a dollar-quoted string follows the same rules
as an unquoted identifier, except that it cannot contain a dollar sign.
Tags are case sensitive, so
is correct, but
$TAG$String content$tag$ is not.
A dollar-quoted string that follows a keyword or identifier must be separated from it by whitespace; otherwise the dollar quoting delimiter would be taken as part of the preceding identifier.
Dollar quoting is not part of the SQL standard, but it is often a more convenient way to write complicated string literals than the standard-compliant single quote syntax. It is particularly useful when representing string constants inside other constants, as is often needed in procedural function definitions. With single-quote syntax, each backslash in the above example would have to be written as four backslashes, which would be reduced to two backslashes in parsing the original string constant, and then to one when the inner string constant is re-parsed during function execution.
|ISBN 9781906966041||The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference||See the print edition|