PostgreSQL: using strings in SQL subquery text in a function

Question

I'm using the pgr_drivingDistance function that requires a SQL subquery as text like so:

pgr_drivingDistance(
'SELECT id, source, target, cost, reverse_cost FROM edges', start_vid, dist, true
)

I would like to subset the edges in the edges table with a where clause like so:

pgr_drivingDistance(
'SELECT id, source, target, cost, reverse_cost FROM edges WHERE col = 'some_string'', start_vid, dist, true
)

The problem is I cannot use single quotes in this case for the string. I tried to escape the quotes of the string with ''', backslash, $$ notations without success.

Is there a way to do this?

You can escape single quote by doubling them, so, make it like `...WHERE col = ''some_str'' ` — Anand Sowmithiran, Jan 04 '23 at 13:59

score 1 · Accepted Answer · answered Jan 04 '23 at 15:54

There's various ways to escape single quotes (apostrophes) in Postgres string literals:

"To include a single-quote character within a string constant, write two adjacent single quotes"
```
pgr_drivingDistance('SELECT … WHERE col = ''some_string'' ', start_vid, dist, true)
```
"A single quote can be included in an escape string by writing \', in addition to the normal way of ''."
```
pgr_drivingDistance(e'SELECT … WHERE col = \'some_string\' ', start_vid, dist, true)
```
"Inside the dollar-quoted string, single quotes can be used without needing to be escaped"
```
pgr_drivingDistance($$SELECT … WHERE col = 'some_string'$$, start_vid, dist, true)
```

PostgreSQL: using strings in SQL subquery text in a function

1 Answers1