Escaping slash 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 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.

Answer 2

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.