Re: [SQL] Question on Escape-string

[Steve Midgley]

At 05:20 AM 1/1/2009, pgsql-sql-ow...@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



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] How to excute dynamically a generated SQL command?

[John Zhang]

Hi the list,

Referring to the PostgreSQL 8.3 documentation " 38.5.4. Executing Dynamic
Commands ", the command for executing a dynamic command is:
EXECUTE command-string [ INTO [STRICT] target ];


I am to execute an sql statement created dynamically, which is represented
in a variable sSql.
Here is an example:
sSql='INSERT INTO hm_raster.hm_airphotos( file_ext, airphoto) VALUES
('.tif',  lo_import( E''C:\\HM\\Data\\Flightmap.tif'');';
EXECUTE sSQL;

It raises the error as:
ERROR:  syntax error at end of input
LINE 1: ...E'C:\\HM\\Data\\Flightmap.tif')
  ^

I would appreciate a lot if you offer your input. Thanks a lot.

John