0

I'm trying to use Query Variables in Grafana, the panel query source is PostgreSQL for QuestDB.

I have added the variable without any issue, but I'm unable to use the variable in Panel query since the variable values contains the spaces (SENSOR01 ON_OFF), also I'm unable to figure-out how to add single quote escape.

Following are the scenarios I tried:

Scenario1: this indicates due to space in the Variable value, on_off considered as separate word

where sensor_name = $sensor

db query error: pq: unexpected token: on_off

. .

Scenario2: tried to add single quotes explicitly for the variable value, but there is generic error from source DB (QuestDB)

where sensor_name = concat('''', $sensor, '''')

db query error: pq: dangling expression

When tried Scenario2 approach directly in query of Variable, getting the same error

..

Scenario3: Hard-coded the variable value with space and with single quotes, but this giving me error with first part of the variable, looks like the hard-coded single quotes not passed here!

enter image description here

Error (Scenario3):
enter image description here

Is there any way/workaround to tackle this issue?

Shekar Kola
  • 1,287
  • 9
  • 15

2 Answers2

2

Could you just add the quotes directly in the query?

where sensor_name = '$sensor'

I have a similar grafana panel querying a questDB database using a variable and it works for me. This is my query:

select device_type, avg(duration_ms) as avg_duration_ms, avg(speed) as avg_speed, avg(measure1) as avg_m1, avg(measure2) as avg_m2 from ilp_test 
  WHERE
  $__timeFilter(timestamp) and device_type = '$deviceType'
Javier Ramirez
  • 3,446
  • 24
  • 31
  • 1
    Yes tried that earlier (but didn't pay attention to it), I don't know why I was thinking that it would go as hard-coded value, but I Insist to try it out based on this answer, and it works!! – Shekar Kola Sep 03 '22 at 12:28
1

A rather hacky workaround would be to do:

where sensor_name = concat(cast(cast('&' as int) + 1 as char), $sensor, cast(cast('&' as int) + 1 as char))

This should work, but I'm pretty sure there is a better solution. Let me find it and get back to you.

Update. We may support Postgres syntax (which is '' escaping for a single quote char) in one of upcoming versions. For now, you'd have to use the above workaround.

Andrei Pechkurov
  • 438
  • 4
  • 10
  • Getting the error `db query error: pq: unbalanced (` in Grafana panel query, but when tried the cast workaround `cast(cast('&' as int) + 1 as char)` from the source DB it return the single quote (`'`) as expected, not sure why the Grafana doesn't accept :-( – Shekar Kola Sep 02 '22 at 12:47