[postgis-users] postgis-users Digest, Vol 163, Issue 11

[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
**********************************************