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
