Thread: Question on Escape-string

Dear all,<br /><br />I am using pl/pgsql to develop a function to implement some logic to load BLOB data, like .tif
file,to postgres DB. The issue I am facing is the file name MUST be with double back-slash \\ in order for pgsql to
processthe string properly. However, when the string is Escaped in my function, how can I pass it in to lo_import()
function?<br /><br />Is there any function to double back-slash a string? Or how can we preserve a string as RAW? <br
/><br/>---- ISSUE :<br />--         use E'C:\\tmp\\tst.tif' for the full file name for IN parameter of load_blob
function.<br/> --         however, when the string is escaped it becomes 'C:\tmp\tst.tif' as expected<br />--        
thefile name need be passed in to lo_import() function again without double \\<br />--         when it is passed in and
escaped, the \ is gone and the filename becomes meaningless <br /><br />Any input would be much appreciated!<br /><br
/>Thanksa lot<br />John<br /> 

At 05:20 AM 1/1/2009, pgsql-sql-owner@postgresql.org wrote:
>To: pgsql-sql@postgresql.org
>Subject: Question on Escape-string
>X-Archive-Number: 200812/132
>X-Sequence-Number: 32082
>
>Dear all,
>
>I am using pl/pgsql to develop a function to implement some logic to 
>load BLOB data, like .tif file, to postgres DB. The issue I am facing 
>is the file name MUST be with double back-slash \\ in order for pgsql 
>to process the string properly. However, when the string is Escaped in 
>my function, how can I pass it in to lo_import() function?
>
>Is there any function to double back-slash a string? Or how can we 
>preserve a string as RAW?
>
>---- ISSUE :
>--         use E'C:\\tmp\\tst.tif' for the full file name for IN 
>parameter of load_blob function.
>--         however, when the string is escaped it becomes 
>'C:\tmp\tst.tif' as expected
>--         the file name need be passed in to lo_import() function 
>again without double \\
>--         when it is passed in and escaped , the \ is gone and the 
>filename becomes meaningless
>
>Any input would be much appreciated!
>
>Thanks a lot
>John

Hi John,

If I understand you, you want to put double backslashes back into a 
string that has been stored in a Postgres field with single 
backslashes?

Here's some SQL I cooked up to demonstrate what I think is a solution. 
Note the use of "\\" and "\\\\" doubly-escaped backslashes in the regex 
replace parameters - that's the key.

DROP TABLE IF EXISTS test;
CREATE TABLE test
(  filename character varying(255) NOT NULL,  data bytea
);

insert into test (filename, data)
values (E'c:\\tmp\\tst.tif', '1234');

select replace(filename, E'\\', E'\\\\'), data from test

Does this do it?

Steve