FYI: <br>The problem lies with "IMMUTABLE STRICT", which should NOT be there after the function body. <br><br>Now the function runs as expected.<br> quote_literal() should be used for the strings with escape chars.<br>
<br>sSql := 'INSERT INTO ' || schemaName || '.' || blob_table || '( ' || quote_ident('file_ext') || ', ' || quote_ident( blob_column) || ') VALUES (' || quote_literal( file_ext ) || ', lo_import( ' || quote_literal(fn) || '))';<br>
EXECUTE sSql;<br><br>Again, thanks for all the responses. <br><br><br><div class="gmail_quote">On Tue, Jan 6, 2009 at 8:44 PM, John Zhang <span dir="ltr"><<a href="mailto:johnzhang06@gmail.com">johnzhang06@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">Hi there, thank you all for the responses on the problem. The problem was that I missed ')' for the VALUES (..). Thank you for pointing that out. <br>
<br>Now, another problem is raised: ERROR: INSERT is not allowed in a non-volatile function.<br>
<br>The language I am using is LANGUAGE 'plpgsql' IMMUTABLE STRICT.<br><br>What should I change to make INSERT allowable?<br><br>Thanks a lot<br><font color="#888888">John</font><div><div></div><div class="Wj3C7c">
<br><br><div class="gmail_quote">On Mon, Jan 5, 2009 at 9:57 AM, Steve Midgley <span dir="ltr"><<a href="mailto:science@misuse.org" target="_blank">science@misuse.org</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">At 10:20 AM 1/4/2009, <a href="mailto:pgsql-sql-owner@postgresql.org" target="_blank">pgsql-sql-owner@postgresql.org</a> wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Message-ID: <<a href="mailto:618950b80901031757l15109658kdae1cdb0814d33fc@mail.gmail.com" target="_blank">618950b80901031757l15109658kdae1cdb0814d33fc@mail.gmail.com</a>><br>
Date: Sat, 3 Jan 2009 17:57:32 -0800<br>
From: "John Zhang" <<a href="mailto:johnzhang06@gmail.com" target="_blank">johnzhang06@gmail.com</a>><div><br>
To: <a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br></div>
Subject: How to excute dynamically a generated SQL command?<br>
X-Archive-Number: 200901/2<br>
X-Sequence-Number: 32084<div><br>
<br>
Hi the list,<br>
<br>
Referring to the PostgreSQL 8.3 documentation " 38.5.4. Executing Dynamic Commands ", the command for executing a dynamic command is:<br>
EXECUTE command-string [ INTO [STRICT] target ];<br>
<br>
<br>
I am to execute an sql statement created dynamically, which is represented in a variable sSql.<br>
Here is an example:<br></div>
sSql='INSERT INTO hm_raster.hm_airphotos( file_ext, airphoto) VALUES ('.tif', lo_import( E''C:\\HM\\Data\\Flightmap.tif'');';<div><br>
EXECUTE sSQL;<br>
<br>
It raises the error as:<br>
ERROR: syntax error at end of input<br>
LINE 1: ...E'C:\\HM\\Data\\Flightmap.tif')<br>
^<br>
<br>
I would appreciate a lot if you offer your input. Thanks a lot.<br>
<br>
John<br>
</div></blockquote>
<br>
<br>
John: You're not escaping all your strings. That error message is a tip-off, I think. Try this line:<br>
<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
sSql='INSERT INTO hm_raster.hm_airphotos( file_ext, airphoto) VALUES (''.tif'', lo_import( E''C:\\HM\\Data\\Flightmap.tif'');';<br>
</blockquote>
<br>
The part I changed was: ''.tif''<br>
<br>
I'm not sure what language you're working in, but it's remotely possibly (depending on the execution stack) that you have to doubly escape your backslashes also, in which case:<br>
<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
sSql='INSERT INTO hm_raster.hm_airphotos( file_ext, airphoto) VALUES (''.tif'', lo_import( E''C:\\\\HM\\\\Data\\\\Flightmap.tif'');';<br>
</blockquote>
<br>
I suffer on Windows wishing we could have "/" path separators by default. Note that these days Windows generally does support "/" instead of "\" for paths if you're careful. If you put them in quotes, it works even on the command line, which is helpful. You can type this directly into the CMD prompt now:<br>
<br>
dir "c:/temp"<br>
<br>
All new programs I write on Windows (in Ruby) use forward slashes for paths, and it works just fine. Not sure about VB or C#, but I'd guess you can make it work. Might be simpler than all the escaping work..<br>
<br>
Best,<br>
<br>
Steve<br><font color="#888888">
<br>
<br>
-- <br>
Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org" target="_blank">pgsql-sql@postgresql.org</a>)<br>
To make changes to your subscription:<br>
<a href="http://www.postgresql.org/mailpref/pgsql-sql" target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br>
</font></blockquote></div><br>
</div></div></blockquote></div><br>