7

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?

sigod
  • 3,514
  • 2
  • 21
  • 44
mohnston
  • 737
  • 1
  • 6
  • 18

2 Answers2

13

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.

Tometzky
  • 22,573
  • 5
  • 59
  • 73
  • I *am* using quote_literal in a function. See my original post. I tried as you suggested but still don't get the correct result. – mohnston Jun 17 '10 at 21:25
  • You don't understand. You're calling this job_page() function wrongly. You need to quote it's argument before calling job_page - there's no way a function will quote it's own argument properly for itself. Paste a code, where you call this function and then we'll be able to help. – Tometzky Jun 18 '10 at 11:44
  • The "standard_conforming_strings" option is enabled by default since PostgreSQL 9.1 (released 2011-09-12). – Tometzky Sep 15 '21 at 08:42
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.

Peter Tillemans
  • 34,983
  • 11
  • 83
  • 114
  • Does not work in this case. If I try replace("inputText", '\\', '\\\\') I get the same result. Evidently pg sees \752 as a special character. (not a backslash) – mohnston Jun 15 '10 at 22:38