Escaping backslash in Postgresql
I'm trying to write an sql function in Postgresql that will parse a file path. I开发者_如何学Python want to return just the file name.
I cannot get past getting an accurate text string in the function.
Here is the function:
Function: job_page("inputText" text)
DECLARE
$5 text;
BEGIN
$5 = quote_literal("inputText");
return $5;
END
When I run this:
select job_page('\\CAD_SVR\CADJOBS\7512-CEDARHURST ELEMENTARY SCHOOL\7512-20.DWG')
I get this result:
"E'\\CAD_SVRCADJOBSé2-CEDARHURST ELEMENTARY SCHOOLé2-20.DWG'"
Postgresql interprets the slash followed by certain characters as a special character.
How do I escape?
You should use escape string syntax:
select E'\\CAD_SVR\\CADJOBS\\7512-CEDARHURST ELEMENTARY SCHOOL\\7512-20.DWG';
\CAD_SVR\CADJOBS\7512-CEDARHURST ELEMENTARY SCHOOL\7512-20.DWG
This will work in any case.
Or you can set standard_conforming_strings=on
and use:
select '\CAD_SVR\CADJOBS\7512-CEDARHURST/ ELEMENTARY SCHOOL\7512-20.DWG';
\CAD_SVR\CADJOBS\7512-CEDARHURST/ ELEMENTARY SCHOOL\7512-20.DWG
quote_literal
function should be used only when you will be constructing a query for exec
call in pl/pgsql function. For constructing a query in a client you should use a client's library quoting function, like PQescapeStringConn
in libpq or pg_escape_string
in PHP. But the best option is to use prepared statements and use a string as an argument, which eliminates all quoting and is much safer too.
You have to escape the \ with another \
i.e. \\
You can change this behavior off by setting the standard_conforming_strings option to on. By default it is off, but this default will change some time in the future.
I recommend the double backslash for the time being.
精彩评论