FYI:
The problem lies with "IMMUTABLE STRICT", which should NOT be there after
the function body.
Now the function runs as expected.
quote_literal() should be used for the strings with escape chars.
sSql := 'INSERT INTO ' || schemaName || '.' || blob_table || '( ' ||
quote_ident('file_ext') || ', ' || quote_ident( blob_column) || ') VALUES ('
|| quote_literal( file_ext ) || ', lo_import( ' || quote_literal(fn) ||
'))';
EXECUTE sSql;
Again, thanks for all the responses.
On Tue, Jan 6, 2009 at 8:44 PM, John Zhang <[email protected]> wrote:
> 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.
>
> Now, another problem is raised: ERROR: INSERT is not allowed in a
> non-volatile function.
>
> The language I am using is LANGUAGE 'plpgsql' IMMUTABLE STRICT.
>
> What should I change to make INSERT allowable?
>
> Thanks a lot
> John
>
>
> On Mon, Jan 5, 2009 at 9:57 AM, Steve Midgley <[email protected]> wrote:
>
>> At 10:20 AM 1/4/2009, [email protected] wrote:
>>
>>> Message-ID: <[email protected]
>>> >
>>> Date: Sat, 3 Jan 2009 17:57:32 -0800
>>> From: "John Zhang" <[email protected]>
>>> To: [email protected]
>>> Subject: How to excute dynamically a generated SQL command?
>>> X-Archive-Number: 200901/2
>>> X-Sequence-Number: 32084
>>>
>>> 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
>>>
>>
>>
>> John: You're not escaping all your strings. That error message is a
>> tip-off, I think. Try this line:
>>
>> sSql='INSERT INTO hm_raster.hm_airphotos( file_ext, airphoto) VALUES
>>> (''.tif'', lo_import( E''C:\\HM\\Data\\Flightmap.tif'');';
>>>
>>
>> The part I changed was: ''.tif''
>>
>> 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:
>>
>> sSql='INSERT INTO hm_raster.hm_airphotos( file_ext, airphoto) VALUES
>>> (''.tif'', lo_import( E''C:\\\\HM\\\\Data\\\\Flightmap.tif'');';
>>>
>>
>> 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:
>>
>> dir "c:/temp"
>>
>> 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..
>>
>> Best,
>>
>> Steve
>>
>>
>> --
>> Sent via pgsql-sql mailing list ([email protected])
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>
>
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
