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
– Anand Sowmithiran, Commented Jan 4, 2023 at 13:59

Bergi · Accepted Answer · 2023-01-04 15:54:28Z

1

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)
```

answered Jan 4, 2023 at 15:54

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: using strings in SQL subquery text in a function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related