Escaping backslash in Postgresql

Question

I'm trying to write an sql function in Postgresql that will parse a file path. I 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?

Answer 1

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.