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

[John Zhang]

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/2acc47fc/attachment.html>