Re: Question on Escape-string

At 05:20 AM 1/1/2009, pgsql-sql-owner(at)postgresql(dot)org wrote:
>To: pgsql-sql(at)postgresql(dot)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