2

Using PostgreSQL/PgRouting syntax, I wrote query like this:

SELECT * FROM shortest_path('SELECT gid  AS id,
      source::int4 AS source,
      target::int4 AS target,
      cost::float8 AS cost,
      reverse_cost::float8 AS reverse_cost
from network where country='USA'  ',                           
257027,
276521,
true,
true)

I got an error:

ERROR:  syntax error at or near "USA"
LINE 6:  from network where country='USA'  ',             
ERROR: syntax error at or near "USA"
SQL state: 42601
Character: 226

So, I create new field in the network table countryInt and I updated field like this:

Update network set countryInt =1 where country='USA'

So, my query is:

SELECT * FROM shortest_path('SELECT gid  AS id,
    source::int4 AS source,
    target::int4 AS target,
    cost::float8 AS cost,
    reverse_cost::float8 AS reverse_cost
from network where countryInt=1 ',                           
257027,
276521,
true,
true)

And now it works.

However, what if I have to put some string in condition, and quotation marks are a problem?

1 Answer 1

6

Escape the single quote with another single quote:

SELECT * FROM shortest_path('SELECT gid  AS id,
      source::int4 AS source,
      target::int4 AS target,
      cost::float8 AS cost,
      reverse_cost::float8 AS reverse_cost
from network where country=''USA''  ',                           
257027,
276521,
true,
true)
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.