[postgis-users] postgis-users Digest, Vol 163, Issue 11
Bistrais, Bob
Bob.Bistrais at maine.gov
Mon Sep 28 13:01:12 PDT 2015
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