[postgis-users] Re: How to excute dynamically a generated SQL command?

[John Zhang]

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 <johnzhang06 at gmail.com> 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 <science at misuse.org> wrote:
>
>> At 10:20 AM 1/4/2009, pgsql-sql-owner at postgresql.org wrote:
>>
>>> Message-ID: <618950b80901031757l15109658kdae1cdb0814d33fc at mail.gmail.com
>>> >
>>> Date: Sat, 3 Jan 2009 17:57:32 -0800
>>> From: "John Zhang" <johnzhang06 at gmail.com>
>>> To: postgis-users at postgis.refractions.net
>>> 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 (pgsql-sql at postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090106/909a1a8c/attachment.html>