[postgis-users] postgis-users Digest, Vol 163, Issue 11
Birgit Laggner
birgit.laggner at ti.bund.de
Mon Sep 28 23:54:47 PDT 2015
Hi Bob,
you are right. What a mess... Now I tried using the quote_literal()
which worked for the select statement:
declare @v;
set @v = '1';
SELECT * from gradeschools_aroo where gradecode = quote_literal(@v);
So, it all comes down to Davids suggestion from the beginning. But the
quote_literal() won't work if not used in a SELECT statement.
Regards,
Birgit
Am 28.09.2015 um 22:01 schrieb Bistrais, Bob:
> Hello Birgit,
>
> I tried triple quoting my value as suggested. This works in the simple script. But when I try using it in a Select statement, it fails:
>
> declare @v;
> set @v = '''1''';
> SELECT * from gradeschools_aroo where gradecode = @v;
>
> SELECT * from gradeschools_aroo where gradecode = ''1''
> ERROR: syntax error at or near "1"
>
>
> -----Original Message-----
> From: Birgit Laggner [mailto:birgit.laggner at ti.bund.de]
> Sent: Monday, September 28, 2015 4:02 AM
> To: Bistrais, Bob; postgis-users at lists.osgeo.org; david.fawcett at gmail.com
> Subject: Re: postgis-users Digest, Vol 163, Issue 11
>
> Hi Bob,
>
> now, I understand your problem. My suggestion would be to triple the single quotation marks as in the small example below:
>
>
> declare @v;
>
> set @v = '''1''';
>
> print @v;
>
>
> I hope this works with your pgscript somehow...
>
> Regards,
>
> Birgit
>
>
> Am 25.09.2015 um 14:19 schrieb Bistrais, Bob:
>> Hi Birgit and David,
>>
>> Many thanks for your replies. I actually tried what you had suggested. Quoting the variable as '@myvariable' caused the liteal string, @myvariable, to be passed. I also tried casting, but the variable still got passed as an integer, without quots, causing an error.
>>
>> In order to make it work, I created another field which is an integer field. I populated the new field with the appropriate integer values. This worked, but I would still be interested in finding a way to pas numeric characters as text.
>>
>>
>>
>> ________________________________________
>> From: postgis-users-bounces at lists.osgeo.org [postgis-users-bounces at lists.osgeo.org] on behalf of postgis-users-request at lists.osgeo.org [postgis-users-request at lists.osgeo.org]
>> Sent: Thursday, September 24, 2015 3:00 PM
>> To: postgis-users at lists.osgeo.org
>> Subject: postgis-users Digest, Vol 163, Issue 11
>>
>> Send postgis-users mailing list submissions to
>> postgis-users at lists.osgeo.org
>>
>> To subscribe or unsubscribe via the World Wide Web, visit
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>> or, via email, send a message with subject or body 'help' to
>> postgis-users-request at lists.osgeo.org
>>
>> You can reach the person managing the list at
>> postgis-users-owner at lists.osgeo.org
>>
>> When replying, please edit your Subject line so it is more specific
>> than "Re: Contents of postgis-users digest..."
>>
>>
>> Today's Topics:
>>
>> 1. Using string variables with PGScript (Bistrais, Bob)
>> 2. Re: Using string variables with PGScript (David Fawcett)
>> 3. Re: Using string variables with PGScript (Birgit Laggner)
>>
>>
>> ----------------------------------------------------------------------
>>
>> Message: 1
>> Date: Wed, 23 Sep 2015 21:26:32 +0000
>> From: "Bistrais, Bob" <Bob.Bistrais at maine.gov>
>> To: "postgis-users at lists.osgeo.org" <postgis-users at lists.osgeo.org>
>> Subject: [postgis-users] Using string variables with PGScript
>> Message-ID:
>> <D655A37D8BC306438F87579A672E3D107AEE9906 at OIT-TEAQEXMBX01.som.w2k.state.me.us>
>>
>> Content-Type: text/plain; charset="us-ascii"
>>
>> I am having trouble using a variable in a Select statement, using PGScript. I am trying to define a string variable, then use it in the Select statement. Something like this:
>>
>> DECLARE @MYSTRING;
>> SET @MYSTRING = CAST(@REC[0]['mycode'] AS STRING); -- at REC was set from a previous select
>>
>> SELECT * from mytable where myfield = @MYSTRING;
>>
>> -The variable is not quoted, so the select statement fails. How do I get the string variable to be enclosed in quotes?
>>
>>
>> -------------- next part --------------
>> An HTML attachment was scrubbed...
>> URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150923/43f67cbd/attachment-0001.html>
>>
>> ------------------------------
>>
>> Message: 2
>> Date: Wed, 23 Sep 2015 20:43:30 -0500
>> From: David Fawcett <david.fawcett at gmail.com>
>> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
>> Subject: Re: [postgis-users] Using string variables with PGScript
>> Message-ID:
>> <CAF7K3b8x=jEBPMWTa_B4hUOSNK=45tE0nM1xcjyS9wWRmoUodQ at mail.gmail.com>
>> Content-Type: text/plain; charset="utf-8"
>>
>> Bob,
>>
>> I haven't ever used PGScript, but I wonder one of the postgres quote
>> functions found on this page might work:
>> http://www.postgresql.org/docs/9.1/static/functions-string.html
>>
>> I would try quote_literal()
>>
>> David.
>>
>> On Wed, Sep 23, 2015 at 4:26 PM, Bistrais, Bob <Bob.Bistrais at maine.gov>
>> wrote:
>>
>>> I am having trouble using a variable in a Select statement, using
>>> PGScript. I am trying to define a string variable, then use it in the
>>> Select statement. Something like this:
>>>
>>>
>>>
>>> DECLARE @MYSTRING;
>>>
>>> SET @MYSTRING = CAST(@REC[0]['mycode'] AS STRING); -- at REC was set from a
>>> previous select
>>>
>>>
>>>
>>> SELECT * from mytable where myfield = @MYSTRING;
>>>
>>>
>>>
>>> -The variable is not quoted, so the select statement fails. How do I get
>>> the string variable to be enclosed in quotes?
>>>
>>>
>>>
>>>
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>
>> -------------- next part --------------
>> An HTML attachment was scrubbed...
>> URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150923/a0ce2539/attachment-0001.html>
>>
>> ------------------------------
>>
>> Message: 3
>> Date: Thu, 24 Sep 2015 15:22:42 +0200
>> From: Birgit Laggner <birgit.laggner at ti.bund.de>
>> To: postgis-users at lists.osgeo.org
>> Subject: Re: [postgis-users] Using string variables with PGScript
>> Message-ID: <5603F922.4090801 at ti.bund.de>
>> Content-Type: text/plain; charset="windows-1252"; Format="flowed"
>>
>> Hi Bob,
>>
>> I would recommend something as simple as
>>
>> SELECT * from mytable where myfield = '@MYSTRING';
>>
>> I tried using a string variable in a PGScript once, too, and if I
>> remember correctly, quote_literal() did not work.
>>
>> Good luck and regards,
>>
>> Birgit
>>
>>
>>
>>
>> Am 24.09.2015 um 03:43 schrieb David Fawcett:
>>> Bob,
>>>
>>> I haven't ever used PGScript, but I wonder one of the postgres quote
>>> functions found on this page might work:
>>> http://www.postgresql.org/docs/9.1/static/functions-string.html
>>>
>>> I would try quote_literal()
>>>
>>> David.
>>>
>>> On Wed, Sep 23, 2015 at 4:26 PM, Bistrais, Bob <Bob.Bistrais at maine.gov
>>> <mailto:Bob.Bistrais at maine.gov>> wrote:
>>>
>>> I am having trouble using a variable in a Select statement, using
>>> PGScript. I am trying to define a string variable, then use it in
>>> the Select statement. Something like this:
>>>
>>> DECLARE @MYSTRING;
>>>
>>> SET @MYSTRING = CAST(@REC[0]['mycode'] AS STRING); -- at REC was
>>> set from a previous select
>>>
>>> SELECT * from mytable where myfield = @MYSTRING;
>>>
>>> -The variable is not quoted, so the select statement fails. How
>>> do I get the string variable to be enclosed in quotes?
>>>
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>
>>>
>>>
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>> -------------- next part --------------
>> An HTML attachment was scrubbed...
>> URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150924/8ed2df10/attachment-0001.html>
>>
>> ------------------------------
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>> End of postgis-users Digest, Vol 163, Issue 11
>> **********************************************
>>
>
>
More information about the postgis-users
mailing list